By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,358 Members | 1,225 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,358 IT Pros & Developers. It's quick & easy.

Repost: postmaster growing to consume all memory

P: n/a
Sorry for the repost. Just wondering if anyone has a workaround for 7.4?

At 11:25 AM 1/27/2004 -0500, Tom Lane wrote:
Martijn van Oosterhout <kl*****@svana.org> writes:
I'm afraid I'll have to defer to someone else (Tom?) as why the

estimate was
out by three orders of magnitude.


I'd like to know that, too.
I'd suggest playing around with statistics and seeing if you can work out
why they were so bad.


Could we see the pg_stats row for the ip_saddr column? Also, does the
estimate get better if you increase the stats target for ip_saddr and
re-analyze?

regards, tom lane


OK - the statistics and various queries follow. If n_distinct is the
estimate of unique values then it is still off, but the plan does change.
How does postgresql do the analysis?

select version();
version
-------------------------------------------------------------
PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)
VACUUM full cust_ulog ;
VACUUM
Time: 96916.323 ms
analyze cust_ulog;
Time: 25969.562 ms
explain select ip_saddr,count(*),sum(ip_totlen) from cust_ulog
where true
group by ip_saddr order by sum(ip_totlen) desc limit 10
QUERY PLAN
---------------------------------------------------------------------------------------
Limit (cost=331739.22..331739.24 rows=10 width=13)
-> Sort (cost=331739.22..331743.03 rows=1523 width=13)
Sort Key: sum(ip_totlen)
-> HashAggregate (cost=331651.09..331658.71 rows=1523 width=13)
-> Seq Scan on cust_ulog (cost=0.00..262156.91
rows=9265891 width=13)
(5 rows)

Time: 163.512 ms

select * from pg_stats where tablename ='cust_ulog' and attname='ip_saddr';
schemaname | tablename | attname | null_frac | avg_width | n_distinct |
most_common_vals
| most_common_freqs |
histogram_bounds | correlation
------------+-----------+----------+-----------+-----------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
public | cust_ulog | ip_saddr | 0 | 11 | 1523 |
{202.185.236.30,202.185.236.6,202.185.236.11,202.1 85.236.25,202.185.236.7,203.121.96.19,202.185.236. 23,202.75.129.102,192.228.128.20,202.188.1.5}
|
{0.243667,0.0926667,0.0553333,0.0433333,0.031,0.01 83333,0.0143333,0.0116667,0.011,0.011}
|
{4.2.49.2,61.149.58.98,66.128.175.107,69.56.63.214 ,195.92.67.209,202.185.231.40,202.185.236.47,203.7 0.228.182,208.41.95.194,212.253.48.34,221.232.160. 6}
| 0.00267716
(1 row)

alter table cust_ulog alter column ip_saddr set statistics 50;
ALTER TABLE
Time: 15.605 ms
analyze cust_ulog;
ANALYZE
Time: 51251.862 ms
select correlation,attname from pg_stats where tablename ='cust_ulog' and
attname='ip_saddr';
correlation | attname
-------------+----------
0.0286273 | ip_saddr

select n_distinct from pg_stats where tablename ='cust_ulog' and
attname='ip_saddr';
n_distinct
------------
6714
(1 row)
Time: 16.134 ms
alter table cust_ulog alter column ip_saddr set statistics 100;
ALTER TABLE
Time: 13.728 ms
analyze cust_ulog;
ANALYZE
Time: 55412.288 ms
select n_distinct from pg_stats where tablename ='cust_ulog' and
attname='ip_saddr';
n_distinct
------------
12939
(1 row)
Time: 26.338 ms

explain select ip_saddr,count(*),sum(ip_totlen) from cust_ulog
where true
group by ip_saddr order by sum(ip_totlen) desc limit 10
;

QUERY PLAN
---------------------------------------------------------------------------------------------
Limit (cost=2096080.34..2096080.37 rows=10 width=13)
-> Sort (cost=2096080.34..2096112.69 rows=12939 width=13)
Sort Key: sum(ip_totlen)
-> GroupAggregate (cost=2002684.66..2095196.65 rows=12939 width=13)
-> Sort (cost=2002684.66..2025796.48 rows=9244729 width=13)
Sort Key: ip_saddr
-> Seq Scan on cust_ulog (cost=0.00..261945.29
rows=9244729 width=13)
(7 rows)

Time: 29.819 ms

select * from pg_stats where tablename ='cust_ulog' and attname='ip_saddr';
schemaname | tablename | attname | null_frac | avg_width | n_distinct |
most_common_vals | most_common_freqs | histogram_bounds | correlation
------------+-----------+----------+-----------+-----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----
-
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------
-----
-
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------
-----
-
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
public | cust_ulog | ip_saddr | 0 | 11 | 12939 |
{202.185.236.30,202.185.236.6,202.185.236.11,202.1 85.236.25,202.185.236.7,203.121.96.19,202.185.236. 23,202.185.236.47,192.228.128.20,202.75.129.102,20 2.188.1.5,202.185.236.3,202.75.129.101,203.121.96. 24,203.121.96.16,202.185.236.34,202.185.236.31,202 .188.0.132,202.188.0.133,202.185.236.12,202.12.28. 131,202.188.1.23,202.188.1.25,202.188.1.4,202.12.2 9.25,202.185.231.40,202.185.236.45,202.185.236.26, 192.228.128.11,193.0.0.193,202.129.169.40,202.146. 72.66,210.186.75.154,219.93.92.82,202.185.236.35,1 92.228.128.1
2,202
..
185.90.118,202.185.236.5,202.185.236.8,202.187.125 .12,211.97.56.133,103.221.179.184,202.185.66.41,20 2.186.47.155,202.186.140.50,202.188.172.163,216.39 .48.40,216.218.158.87,216.218.201.91,216.218.224.2 6,216.219.254.60,202.185.254.231,202.186.49.133,20 2.187.31.12,202.187.58.165,202.188.0.164,203.121.1 5.140,203.193.96.68,207.218.165.207,61.6.38.139,61 .175.132.228,62.62.139.253,63.163.210.227,64.61.25 .155,66.48.78.31}
|
{0.248767,0.0997333,0.0566667,0.0382667,0.0345667, 0.0147667,0.0138,0.0118,0.0107667,0.00983333,0.009 46667,0.0093,0.00926667,0.00903333,0.00793333,0.00 776667,0.0077,0.0074,0.00733333,0.0049,0.00443333, 0.0044,0.0044,0.00426667,0.00406667,0.0038,0.0031, 0.0028,0.00193333,0.0018,0.0016,0.00146667,0.0014, 0.00123333,0.00113333,0.0011,0.00103333,0.0008,0.0 008,0.000733333,0.0006,0.0006,0.000566667,0.000566 667,0.000566667,0.000533333,0.000533333,0.00046666 7,0.000433333,0.000433333,0.0004,0.000366667,0.000 366667,0.000
333,0
.. 000133333} |
{4.3.7.155,4.40.42.46,12.8.192.29,12.148.209.196,1 8.206.0.76,24.81.163.171,24.145.188.188,24.194.25. 199,24.228.82.49,61.6.98.138,61.6.154.62,61.48.54. 46,61.88.0.6,61.149.232.192,61.222.96.205,62.12.11 2.134,63.13.136.190,63.185.112.170,64.40.227.22,64 .146.63.170,64.218.231.6,64.231.230.239,65.43.172. 73,65.92.251.30,65.124.135.2,65.220.24.19,66.50.11 .33,66.92.93.137,66.142.230.36,66.190.201.9,66.250 .5.182,67.39.64.151,67.72.200.129,67.119.178.208,6 7.227.12.5,68.47.247.99,68.76.186.67,68.97.15.56,6 8.146.69.171
..22,2
0
7.206.201.250,208.188.24.184,209.122.233.245,210.3 .135.230,210.54.85.168,210.169.237.132,210.186.123 .51,210.187.228.241,210.201.230.58,211.24.46.39,21 1.104.217.229,211.173.53.3,212.162.76.197,213.152. 72.122,216.68.94.36,216.176.89.162,217.42.54.38,21 7.233.45.74,218.47.249.76,218.117.84.68,218.244.59 .57,219.92.84.8,219.93.108.1,219.95.1.248,219.95.1 64.90,219.109.238.196,219.252.67.13,220.201.96.35, 221.232.160.6}
| 0.0258759
(1 row)

Time: 4.600 ms

select count(*) from cust_ulog ;
count
---------
9461332
(1 row)

Time: 46468.211 ms
fwlogs=# explain select count(distinct(ip_saddr)) from cust_ulog;
QUERY PLAN
---------------------------------------------------------------------------
Aggregate (cost=285057.11..285057.11 rows=1 width=11)
-> Seq Scan on cust_ulog (cost=0.00..261945.29 rows=9244729 width=11)
(2 rows)

Time: 1.087 ms
fwlogs=# select count(distinct(ip_saddr)) from cust_ulog;
count
---------
1391110
(1 row)

Time: 226998.021 ms
***Version 7.3.4 statistics:
select * from pg_stats where tablename ='cust_ulog' and attname='ip_saddr'
schemaname | tablename | attname | null_frac | avg_width | n_distinct |
most_common_vals
| most_common_freqs |
histogram_bounds | correlation
------------+-----------+----------+-----------+-----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
public | hlbb_ulog | ip_saddr | 0 | 12 | 1643 |
{202.185.236.30,202.185.236.6,202.185.236.11,202.1 85.236.25,202.185.236.7,203.121.96.19,202.185.236. 23,192.228.128.20,202.185.236.47,202.188.0.133}
|
{0.238333,0.091,0.0536667,0.037,0.035,0.0163333,0. 015,0.012,0.0103333,0.00966667}
|
{4.2.49.3,61.6.158.105,66.44.2.9,74.16.241.113,192 .228.128.11,202.108.249.21,202.185.236.34,203.69.1 9.3,209.112.0.54,216.87.206.244,221.199.145.131}
| 0.0245979

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #1
Share this Question
Share on Google+
2 Replies

P: n/a
Lincoln Yeoh <ly***@pop.jaring.my> writes:
Sorry for the repost. Just wondering if anyone has a workaround for 7.4?


Brute force way is "set enable_hashagg = off"

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 22 '05 #2

P: n/a
Sounds like that'll work. Sorry I didn't go look for it <sheepish grin>.

Thanks!

At 02:03 PM 2/8/2004 -0500, Tom Lane wrote:
Lincoln Yeoh <ly***@pop.jaring.my> writes:
Sorry for the repost. Just wondering if anyone has a workaround for 7.4?


Brute force way is "set enable_hashagg = off"

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.