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?
