Re: postmaster growing to consume all memory

At 11:25 AM 1/27/2004 -0500, Tom Lane wrote:
>Martijn van Oosterhout <kleptog(at)svana(dot)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.185.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.0183333,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.70.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.185.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,202.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.29.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,192.228.128.18,192.5.6.32,192.42.93.32,200.203.120.200,202.108.249.21,4.2.49.3,4.2.49.4,4.2.49.2,198.133.199.110,210.187.94.28,192.33.14.32,192.41.162.32,192.31.80.32,202.185.236.66,203.106.241.245,203.121.16.85,175.95.81.229,192.149.252.22,211.96.135.202,202.12.27.33,210.13.22.79,45.168.72.70,64.158.165.60,202.186.13.175,108.204.196.246,202.185.236.24,202.186.13.31,202.188.0.147,202.188.0.161,210.187.125.251,221.7.129.98,40.18.72.52,64.159.93.121,89.224.158.214,134.96.65.73,192.26.92.3
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,202.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.26,216.219.254.60,202.185.254.231,202.186.49.133,202.187.31.12,202.187.58.165,202.188.0.164,203.121.15.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.00946667,0.0093,0.00926667,0.00903333,0.00793333,0.00776667,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.0008,0.000733333,0.0006,0.0006,0.000566667,0.000566667,0.000566667,0.000533333,0.000533333,0.000466667,0.000433333,0.000433333,0.0004,0.000366667,0.000366667,0.000366667,0.000333333,0.000333333,0.0003,0.0003,0.0003,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000233333,0.000233333,0.000233333,0.000233333,0.000233333,0.000233333,0.000233333,0.000233333,0.000233333,0.000233333,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000133333,0.000133333,0.000133333,0.000133333,0.000133
333,0
.
000133333} |
{4.3.7.155,4.40.42.46,12.8.192.29,12.148.209.196,18.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.112.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,67.227.12.5,68.47.247.99,68.76.186.67,68.97.15.56,68.146.69.171,68.165.132.130,68.219.173.67,69.132.203.225,80.183.122.245,81.99.56.14,81.241.1.247,129.7.248.124,138.86.163.123,141.158.104.68,150.159.224.8,161.116.78.69,162.40.43.162,166.33.230.44,172.188.219.104,193.11.230.177,195.40.200.67,198.92.157.40,200.61.135.54,202.12.31.140,202.106.127.90,202.158.127.239,202.183.178.133,202.185.104.121,202.186.134.9,202.188.40.29,202.188.141.103,202.190.116.25,203.94.76.166,203.106.237.130,203.127.151.115,203.218.24.4,204.238.120.5,206.239.188
.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,211.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,217.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.164.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.185.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.19.3,209.112.0.54,216.87.206.244,221.199.145.131}
| 0.0245979