postmaster growing to consume all memory

[Lincoln Yeoh]

Hi,

I recently upgraded to postgresql 7.4 and I am having a problem with
postmaster using lots of memory for a query (keeps growing even up to
400MB+ till I stop postgresql ). I don't recall this ever happening with
7.3 with the exact same query but on different data (just as much data tho,
or even more). Happened on 7.4 so I upgraded to 7.4.1. Is HashAggregate
something new in 7.4? If I have time and disk space I'll downgrade to 7.3.3
and see if it happens there. How do I configure a memory consumption limit
on a 7.4 postgresql without killing it?

I believe postgresql.conf is 7.4.1 default.
md5sum= 75ffabc3e90457bd9d6e4ce649e17b6e postgresql.conf

Problem query:
select ip_saddr,count(*),sum(ip_totlen) from cust_ulog
where true
group by ip_saddr order by sum(ip_totlen) desc limit 10

Results from Explain:
Limit (cost=335158.05..335158.08 rows=10 width=13)
-> Sort (cost=335158.05..335161.65 rows=1440 width=13)
Sort Key: sum(ip_totlen)
-> HashAggregate (cost=335075.31..335082.51 rows=1440 width=13)
-> Seq Scan on cust_ulog (cost=0.00..264115.32
rows=9461332 width=13)
(5 rows)

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

Time: 51922.612 ms

Table definition:
Table "public.cust_ulog"
Column | Type | Modifiers
--------------+-----------------------------+-----------
id | integer |
oob_time_sec | timestamp without time zone |
oob_prefix | text |
oob_in | text |
oob_out | text |
ip_saddr | inet |
ip_daddr | inet |
ip_totlen | smallint |
ip_ttl | smallint |
ip_id | integer |
ip_protocol | smallint |
ip_tos | smallint |
tcp_sport | integer |
tcp_dport | integer |
tcp_seq | bigint |
tcp_ack_seq | bigint |
tcp_ack | boolean |
tcp_rst | boolean |
tcp_psh | boolean |
tcp_syn | boolean |
tcp_fin | boolean |
tcp_window | integer |
tcp_urgp | integer |
udp_sport | integer |
udp_dport | integer |
udp_len | smallint |
icmp_type | smallint |
icmp_code | smallint |
icmp_echoid | bigint |
icmp_echoseq | bigint |

psql -V
psql (PostgreSQL) 7.4.1
contains support for command-line editing

select version();
version
-------------------------------------------------------------
PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

Output from: top
3:13pm up 1:14, 5 users, load average: 0.90, 0.33, 0.36
81 processes: 79 sleeping, 2 running, 0 zombie, 0 stopped
CPU states: 18.5% user, 8.3% system, 0.0% nice, 73.0% idle
Mem: 254572K av, 250124K used, 4448K free, 0K shrd, 288K buff
Swap: 522072K av, 92756K used, 429316K free 21048K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
16084 postgres 18 0 258M 217M 23868 D 24.7 87.6 0:20 postmaster

Output from: vmstat 2
procs memory swap io system
cpu
r b w swpd free buff cache si so bi bo in cs us
sy id
0 1 0 71720 4388 504 18172 328
154 1629 200 171 123 10 2 87
1 0 0 75560 4352 464 19492 2154 1416
10730 1416 442 401 21 11 67
1 0 2 77992 4444 456 20568 2062
1498 9170 1504 412 373 13 9 78
0 1 0 80552 4372 284 20944 1878 1412
11158 1426 430 485 17 8 75
0 1 0 81704 4456 284 21884 1946
956 9114 956 395 345 13 8 79
1 0 0 83496 4408 292 22420 2096 412
10864 418 423 406 18 8 74
1 0 0 85540 4384 304 21936 1664
1218 9828 1218 400 387 16 9 75
0 1 0 86140 4352 296 22688 1672 522
11016 528 422 370 18 7 75
1 0 0 87696 4376 288 21376 1998 852
11086 856 437 393 19 8 73
0 1 0 89592 4444 288 21900 1686 1132
12118 1132 434 416 21 8 71
1 0 0 92512 4360 296 21396 1792 1262
11456 1268 429 393 18 5 77
0 2 1 94316 4324 292 21640 2172 508
10644 508 407 402 16 11 72
0 1 0 97160 4436 300 23504 1752 1302
12548 1308 427 455 24 9 67
1 0 0 100096 4428 292 23660 1542 1994
10374 1994 409 373 14 8 78
1 0 0 102848 4400 292 23700 2022 1708
10280 1708 406 365 12 9 79
1 0 0 105620 4456 292 25524 2028 1434
12396 1434 431 435 22 8 69
0 1 0 108248 4404 292 26200 2162 1068
12402 1068 437 409 24 8 68
--


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Martijn van Oosterhout]

On Tue, Jan 27, 2004 at 03:42:09PM +0800, Lincoln Yeoh wrote:

Hi,

I recently upgraded to postgresql 7.4 and I am having a problem with
postmaster using lots of memory for a query (keeps growing even up to
400MB+ till I stop postgresql ). I don't recall this ever happening with
7.3 with the exact same query but on different data (just as much data tho,
or even more). Happened on 7.4 so I upgraded to 7.4.1. Is HashAggregate
something new in 7.4? If I have time and disk space I'll downgrade to 7.3..3
and see if it happens there. How do I configure a memory consumption limit
on a 7.4 postgresql without killing it?
How many distinct IPs are there? The planner estimating around 1500.

Is it much more than that?

--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ (... have gone from d-i being barely usable even by its developers
anywhere, to being about 20% done. Sweet. And the last 80% usually takes
20% of the time, too, right?) -- Anthony Towns, debian-devel-announce

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFAFiQ5Y5Twig3Ge+YRAuI3AJ0fEYMbeNHxhoBkxdX5wF +b1VYgogCeNoDm
2r2/xPjVj5970b4I0o5bSw0=
=BVHn
-----END PGP SIGNATURE-----

[Lincoln Yeoh]

There appear to be 1391110 distinct IPs (using 7.3.4). I did a vacuum full
analyze when on 7.4 after loading the data tho.

What is supposed to happen if the planner guesses right?

Would using 7.4 to get the number of distinct IPs trigger the same problem tho?

Anyway I've downgraded to 7.3.4 (dump and restore), and the problem doesn't
occur for same query, same data - postmaster stays around 4.4MB.

Is there a better way to do my query? That particular one is to get the top
ten source ips by bandwidth. But I've other similar ones. In fact the
reason why I upgraded to 7.4 was I thought the new stuff (joins etc) would
make things faster :).

--- Downgraded ---
PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC 2.96

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=2992108.50..2992108.53 rows=10 width=34)
-> Sort (cost=2992108.50..2994473.84 rows=946133 width=34)
Sort Key: sum(ip_totlen)
-> Aggregate (cost=2738283.50..2832896.82 rows=946133 width=34)
-> Group (cost=2738283.50..2785590.16 rows=9461332 width=34)
-> Sort (cost=2738283.50..2761936.83 rows=9461332
width=34)
Sort Key: ip_saddr
-> Seq Scan on
cust_ulog (cost=0.00..264077.32 rows=9461332 width=34)
(8 rows)

At 07:41 PM 1/27/2004 +1100, Martijn van Oosterhout wrote:

On Tue, Jan 27, 2004 at 03:42:09PM +0800, Lincoln Yeoh wrote:

Hi,

I recently upgraded to postgresql 7.4 and I am having a problem with
postmaster using lots of memory for a query (keeps growing even up to
400MB+ till I stop postgresql ). I don't recall this ever happening with
7.3 with the exact same query but on different data (just as much data

tho,

or even more). Happened on 7.4 so I upgraded to 7.4.1. Is HashAggregate
something new in 7.4? If I have time and disk space I'll downgrade to

7.3.3

and see if it happens there. How do I configure a memory consumption limit
on a 7.4 postgresql without killing it?

How many distinct IPs are there? The planner estimating around 1500.

Is it much more than that?

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

[Martijn van Oosterhout]

On Tue, Jan 27, 2004 at 06:48:49PM +0800, Lincoln Yeoh wrote:

There appear to be 1391110 distinct IPs (using 7.3.4). I did a vacuum full
analyze when on 7.4 after loading the data tho.

What is supposed to happen if the planner guesses right?
Aah, it seems the statistics were *way* off. It was using the new
HashAggregate code which is more efficient than the Aggregate/Group/Sort
combo when there are a small number of groups. When there are a large number
it takes a lot of memory and gets pretty slow.

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 suggest playing around with statistics and seeing if you can work out
why they were so bad.

Hope this helps,
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ (... have gone from d-i being barely usable even by its developers
anywhere, to being about 20% done. Sweet. And the last 80% usually takes
20% of the time, too, right?) -- Anthony Towns, debian-devel-announce

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFAFkZYY5Twig3Ge+YRAuliAKDA3v/nMVHATQk5tvN+djp/zfpVZQCgkOoO
4OaoR0ch90z+0MUOJP+ge28=
=tv3o
-----END PGP SIGNATURE-----

[Lincoln Yeoh]

[my original reply to this hasn't appeared so I'm posting a shorter one]
At 07:41 PM 1/27/2004 +1100, Martijn van Oosterhout wrote:

on a 7.4 postgresql without killing it?

How many distinct IPs are there? The planner estimating around 1500.

Is it much more than that?

Yes, about a million distinct IPs.

I downgraded to 7.3.4 and the problem doesn't occur - postmaster stays
about 4.4MB for the same query and same data. I did a vacuum full analyze
with 7.4. Whereas with 7.3 I did a vacuum full, followed by an analyze.

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

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

[Tom Lane]

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

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

[Lincoln Yeoh]

I'll try to look into that. I'm doing the processing on 7.3.4, so I'll have
to find some spare resources for 7.4.1. Maybe it was a fluke or something.
I did an "overwrite" make install into the same directory as the original
7.3.4 (new data directory though), so maybe I shouldn't have done that?

However, is there a way to get postgresql to handle this more gracefully?
E.g. once it starts using more than max mem it switches or
aborts_and_switches to a more disk based method? Doesn't look easy tho ;).

No offense intended but I doubt the estimator will get things right all the
time (esp if my built-in Murphy Field Intensifier happens to be on).

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?

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

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

[Tom Lane]

Lincoln Yeoh <ly***@pop.jaring.my> writes:

No offense intended but I doubt the estimator will get things right all the
time (esp if my built-in Murphy Field Intensifier happens to be on).

Doesn't mean we shouldn't strive to improve it. The present code for
estimating number of groups is new in 7.4 and I'm sure there are things
that need to be done to it. I'd like to think that factor-of-1000
errors on simple cases are not the best we can do.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Lincoln Yeoh]

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.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,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.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.00 0233333,0.000233333,0.000233333,0.000233333,0.0002 33333,0.000233333,0.0002,0.0002,0.0002,0.0002,0.00 02,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.00 0133333,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,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,68.165.132.130,68.219.173.67,69.132.2 03.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.2 24.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.1 57.40,200.61.135.54,202.12.31.140,202.106.127.90,2 02.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.1 16.25,203.94.76.166,203.106.237.130,203.127.151.11 5,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,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