Обсуждение: Hash Aggregate plan picked for very large table == out of memory

With Postgresql 8.1.9 -- I have a simple group by query:

SELECT target_page_id, min(created_at)
FROM page_page_link
GROUP BY 1;

The page_page_link table has ~130 million rows.

After analyzing the table, the planner picks a hash aggregate plan, which results in an out of memory error.

crystal=> analyze page_page_link;
ANALYZE
crystal=> explain
crystal-> SELECT target_page_id as page_id, min(created_at) as created_at
crystal-> FROM page_page_link
crystal-> GROUP By 1
crystal-> ;
                                    QUERY PLAN                                    
-----------------------------------------------------------------------------------
 HashAggregate  (cost=3663517.88..3670393.09 rows=550017 width=12)
   ->  Seq Scan on page_page_link  (cost=0.00..2993649.92 rows=133973592 width=12)
(2 rows)

The default_statistics_target was originally 200.
I upped it to 1000 and still get the same results.

crystal=> show default_statistics_target;
 default_statistics_target
---------------------------
 1000
(1 row)

crystal=> set enable_hashagg = off;
SET
crystal=> explain
crystal-> SELECT target_page_id as page_id, min(created_at) as created_at
crystal-> FROM page_page_link
crystal-> GROUP BY 1
crystal-> ;
                                       QUERY PLAN                                       
-----------------------------------------------------------------------------------------
 GroupAggregate  (cost=27240841.37..28252518.53 rows=550017 width=12)
   ->  Sort  (cost=27240841.37..27575775.35 rows=133973592 width=12)
         Sort Key: target_page_id
         ->  Seq Scan on page_page_link  (cost= 0.00..2993649.92 rows=133973592 width=12)
(4 rows)

crystal=>

I am working around this by setting enable_hashagg = off  -- but it just seems like a case where the planner is not picking the strategy?

Is there another setting I can change to help make better decisions?

thanks in advance,

Mason

"Mason Hale" <masonhale@gmail.com> writes:
> SELECT target_page_id, min(created_at)
> FROM page_page_link
> GROUP BY 1;

> The page_page_link table has ~130 million rows.

> After analyzing the table, the planner picks a hash aggregate plan, which
> results in an out of memory error.

What have you got work_mem set to?  What's the actual number of groups
(target_page_id values)?

            regards, tom lane

Thanks Tom. Here's more info:

I should have mentioned this previously: running the same query against the same data on 8.1.5 does not result in a hash aggregate plan or an out of memory error. (Note: the hardware is different but very similar -- the main difference is the 8.1.9 server (with the error) has faster disks)

"Mason Hale" <masonhale@gmail.com> writes:
>> What's the actual number of groups
>> (target_page_id values)?

> Approximately 40 million (I'll have a more precise number when the query
> finishes running ).

Ouch.  The discrepancy between that and the 550K estimate is what's
killing you --- the hash table will be 80 times bigger than the planner
thinks.  You sure that increasing the stats target doesn't improve
matters?   (I suppose the distribution is one of these "long tail"
affairs...)

The best solution might be to reduce work_mem --- 40MB is fairly high
for a general-purpose setting anyway IMO, since you need to worry about
complex queries eating multiples of work_mem.

            regards, tom lane

"Mason Hale" <masonhale@gmail.com> writes:

> The default_statistics_target was originally 200.
> I upped it to 1000 and still get the same results.

You did analyze the table after upping the target right? Actually I would
expect you would be better off not raising it so high globally and just
raising it for this one table with

    ALTER [ COLUMN ] column SET STATISTICS integer

> I am working around this by setting enable_hashagg = off  -- but it just
> seems like a case where the planner is not picking the strategy?

Sadly guessing the number of distinct values from a sample is actually a
pretty hard problem. How many distinct values do you get when you run with
enable_hashagg off?

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com