Re: BUG #2225: Backend crash -- BIG table - Mailing list pgsql-bugs

From Patrick Rotsaert
Subject Re: BUG #2225: Backend crash -- BIG table
Date
Msg-id 43E3A30C.5080800@arrowup.be
Whole thread Raw
In response to Re: BUG #2225: Backend crash -- BIG table  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: BUG #2225: Backend crash -- BIG table
List pgsql-bugs
>The problem is that the HashAgg will have to maintain a counter for
>every distinct value of trid, not just those that occur more than
>once.  So if there are a huge number of one-time-only values you could
>still blow out memory (and HashAgg doesn't currently know how to spill
>to disk).
>
>
One-time-only values are in my case more probable, so it will use a lot
of counters.

>That "rows=200" estimate looks suspiciously like a default.  Has this
>table been ANALYZEd recently?  I'd expect the planner not to choose
>HashAgg if it has a more realistic estimate of the number of groups.
>
>            regards, tom lane
>
>
I did a vacuum analyze, now the explain gives different results.

pointspp=# vacuum analyze;
VACUUM

pointspp=# explain select trid, count(*) from pptran group by trid
having count(*) > 1;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 GroupAggregate  (cost=9842885.29..10840821.57 rows=36288592 width=18)
   Filter: (count(*) > 1)
   ->  Sort  (cost=9842885.29..9933606.77 rows=36288592 width=18)
         Sort Key: trid
         ->  Seq Scan on pptran  (cost=0.00..1039725.92 rows=36288592
width=18)
(5 rows)

pointspp=# select trid, count(*) from pptran group by trid having
count(*) > 1;
ERROR:  could not write block 661572 of temporary file: No space left on
device
HINT:  Perhaps out of disk space?

I have 5.1GB of free disk space. If this is the cause, I have a
problem... or is there another way to extract (and remove) duplicate rows?

pgsql-bugs by date:

Previous
From: Patrick Rotsaert
Date:
Subject: Re: BUG #2225: Backend crash -- BIG table
Next
From: "Kai Ronan"
Date:
Subject: BUG #2236: extremely slow to get unescaped bytea data from db