[PERFORM] Query Planner not choosing hash_aggregate appropriately.

The query optimizer fails to use a hash aggregate most of the time.  This is an inconsistent behavior -- the queries below were happily using hash_aggregate on a previous pg_restore from the data.

On one particular class of tables this is especially painful.  The example table has 25 million rows, and when aggregating on a column that the optimizer expects only a few unique values, it chooses a full sort of those 25 million rows before a group aggregate, rather than using a hash aggregate that would be 2 to 4 orders of magnitude faster and use less memory.

The simple statement of this bug is the following EXPLAIN output and corresponding output from the statistics tables.  The actual query used has a more complicated GROUP BY and aggregation (and joins, etc), but if it can't get the most simple version of a sub query correct, of course the composite will be worse.

The condition will occur for any column used to group by regardless of the estimated # of unique items on that column.  Even one that has only two unique values in a 25 million row table.

rr=# explain SELECT  count(distinct v_guid) as view_count, p_type FROM p_log.creative_display_logs

_012_2008_06_15 GROUP BY p_type;                                                                                                                         QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=5201495.80..5395385.38 rows=7 width=47)
   ->  Sort  (cost=5201495.80..5266125.63 rows=25851932 width=47)
         Sort Key: p_type
         ->  Seq Scan on creative_display_logs_012_2008_06_15  (cost=0.00..1223383.32 rows=25851932 width=47)

rr=# select attname, null_frac, avg_width,n_distinct
,correlation from pg_stats where tablename='creative_display_logs_012_2008_06_15' and attname in ('g_id', 'p_type', 'strat', 'datetime', 'ext_s_id', 't_id');
    attname     | null_frac | avg_width | n_distinct | correlation
----------------+-----------+-----------+------------+--------------
 g_id       |         0 |         8 |         14 |     0.221548
 p_type |         0 |         4 |          7 |     0.350718
 datetime       |         0 |         8 |      12584 |     0.977156
 ext_s_id |     0.001 |        38 |      11444 | -0.000842848
 strat       |         0 |        13 |         11 |     0.147418
 t_id       |         0 |         8 |          2 |     0.998711

(5 rows)

I have dumped, dropped, and restored this table twice recently.  Both times followed by a full vacuum analyze.  And in both cases the query optimizer behaves differently.   In one case the poor plan only occures when using the partition table inheritance facade rather than the direct-to-table version above.  In the other case (the current condition), all variants on the query are bad.
This definitely occurs in general and its reproducibility is affected by partitioning  but not dependent on it as far as I can tell.

The database is tuned with the default optimizer settings for 8.3.3 plus constraint exclusion for the partition tables enabled.  Yes, hash_agg is on (actually, commented out so the default of on is active, verified in pg_settings)

The configuration has ample RAM and all the memory tuning parameters are generous (shared_mem 7g, temp space 200m, sort/agg space 500m -- I've tried various settings here with no effect on the plan, just the execution of it w.r.t. disk based sort or mem based sort).


The table definition is the following, if that helps:
       Column       |            Type             | Modifiers
--------------------+-----------------------------+-----------
 v_guid             | character varying(255)      |
 site_id            | bigint                      |
 c_id               | bigint                      |
 item_id            | bigint                      |
 creative_id        | bigint                      |
 camp_id            | bigint                      |
 p_type             | integer                     |
 datetime           | timestamp without time zone |
 date               | date                        |
 ext_u_id           | character varying(50)       |
 ext_s_id           | character varying(50)       |
 u_guid             | character varying(50)       |
 strat              | character varying(50)       |
 sub_p_type         | character varying(32)       |
 exp_id             | bigint                      |
 t_id               | bigint                      |
 htmlpi_id          | bigint                      |
 p_score            | double precision            |


Of course DB hints would solve this.  So would some sort of tuning parameter that lets you dial up or down the tendency to do a hash aggregate rather than a full sort followed by a group aggregate.  This is broken rather severely, especially in combination with partitions (where it is about 3x as likely to fail to use a hash_aggregate where appropriate in limited experiments so far -- there are a few thousand partition tables).

All I want is it to stop being brain-dead and deciding to sort large tables to produce aggregates.  In fact, given the rarity in which a sort is preferred over a hash_agg with large tables, and the tendancy for aggregates to reduce the count by a factor of 10 or more -- i'd turn off the group aggregate if possible!

Thanks for any help!

-Scott