In keeping with some of the recent threads regarding the planner...
I have a fair sized data warehouse in which I am trying to perform an aggregation, but getting OOM errors in Postgres(8.2.4).
I believe the reason for the OOM is that Postgres is attempting to do a hash aggregation, but it has grossly underestimated the rows resulting from the aggregation.
The data in the database is very uniformly distributed so I don't believe that the table stats are the cause of the problem.
This may be related to table inheritance, and can be demonstrated pretty easily.
CREATE TABLE foo(a INT);ANALYZE foo;
CREATE TABLE foo_1() INHERITS(foo);insert into foo_1 select generate_series(1,100000);insert into foo_1 select generate_series(1,100000);insert into foo_1 select generate_series(1,100000);ANALYZE foo_1;
CREATE TABLE foo_2() INHERITS(foo);insert into foo_2 select generate_series(1,100000);insert into foo_2 select generate_series(1,100000);insert into foo_2 select generate_series(1,100000);ANALYZE foo_2;
-- If I query a particular partition, the plan estimate for the hash aggregate is good
EXPLAIN ANALYZE SELECT a,COUNT(*) from foo_1 group by a;
HashAggregate (cost=5822.00..7061.01 rows=99121 width=4) (actual time=554.556..657.121 rows=100000 loops=1) -> Seq Scan on foo_1 (cost=0.00..4322.00 rows=300000 width=4) (actual time=0.014..203.290 rows=300000 loops=1) Total runtime: 712.211 ms
-- If I query the base table, the plan estimate for the hash aggregate is off by several orders of magnitude
EXPLAIN ANALYZE SELECT a,COUNT(*) from foo group by a;
HashAggregate (cost=11686.10..11688.60 rows=200 width=4) (actual time=1724.188..1826.630 rows=100000 loops=1) -> Append (cost=0.00..8675.40 rows=602140 width=4) (actual time=0.016..1045.134 rows=600000 loops=1) -> Seq Scan on foo (cost=0.00..31.40 rows=2140 width=4) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on foo_1 foo (cost=0.00..4322.00 rows=300000 width=4) (actual time=0.012..205.130 rows=300000 loops=1) -> Seq Scan on foo_2 foo (cost=0.00..4322.00 rows=300000 width=4) (actual time=0.011..203.542 rows=300000 loops=1) Total runtime: 1879.550 ms(6 rows)
-- Is there something magical about the hash aggregate estimate of 200 rows?
-- I can have 30,000 or 300,000 rows in each child partition table and multiple partition's with different values of "a" and yet it always come up with 200.
-- eg.
create table foo_3() inherits(foo);insert into foo_3 select generate_series(100000,300000);analyze foo_3;
EXPLAIN ANALYZE SELECT a,COUNT(*) from foo group by a;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------ HashAggregate (cost=15568.12..15570.62 rows=200 width=4) (actual time=2386.813..2691.254 rows=300000 loops=1) -> Append (cost=0.00..11557.41 rows=802141 width=4) (actual time=0.016..1403.121 rows=800001 loops=1) -> Seq Scan on foo (cost=0.00..31.40 rows=2140 width=4) (actual time=0.002..0.002 rows=0 loops=1) -> Seq Scan on foo_1 foo (cost=0.00..4322.00 rows=300000 width=4) (actual time=0.013..201.549 rows=300000 loops=1) -> Seq Scan on foo_2 foo (cost=0.00..4322.00 rows=300000 width=4) (actual time=0.010..211.332 rows=300000 loops=1) -> Seq Scan on foo_3 foo (cost=0.00..2882.01 rows=200001 width=4) (actual time=0.011..137.262 rows=200001 loops=1) Total runtime: 2851.990 ms
Is this a bug, or some subtlety of the Postgres query planner?
In my particular case, I am doing a join out to another small table as part of the aggregation and using constraint exclusion on the partitions, but I believe the cause of my problem is the same.
I am running on 64bit FreeBSD/Postgres 8.2.4 on a machine with 8GB of memory.
An explain of the query and resulting OOM diagnostics follow:
The aggregation will result in 5,000,000 rows, not 5,000.In the stats_dtl table there are 12 observations(hourly) for each customerThere are 125 different policy_id and 25 different policy_group_id'sPolicy's and policy_groups are even distributed across all customers
userquery Scan table_4760 (cost=2897243.60..2897418.60 rows=5000 width=152) -> HashAggregate (cost=2897243.60..2897368.60 rows=5000 width=40) -> Hash Join (cost=7.81..2241002.00 rows=37499520 width=40) Hash Cond: (public.customer_stats_dtl.policy_id = policy.policy_id) -> Append (cost=0.00..1641001.87 rows=59999232 width=40) -> Seq Scan on customer_stats_dtl (cost=0.00..22.45 rows=4 width=40) Filter: ((period_start >= '2007-09-08 20:00:00-04'::timestamp with time zone) AND (period_start < '2007-09-09 08:00:00-04'::timestamp with time zone)) -> Seq Scan on customer_stats_dtl_027 customer_stats_dtl (cost=0.00..1640979.42 rows=59999228 width=40) Filter: ((period_start >= '2007-09-08 20:00:00-04'::timestamp with time zone) AND (period_start < '2007-09-09 08:00:00-04'::timestamp with time zone)) -> Hash (cost=6.25..6.25 rows=125 width=8) -> Seq Scan on policy (cost=0.00..6.25 rows=125 width=8) TopMemoryContext: 268400 total in 32 blocks; 20912 free (43 chunks); 247488 usedunnamed prepared statement: 2097152 total in 8 blocks; 721456 free (2 chunks); 1375696 usedTopTransactionContext: 8192 total in 1 blocks; 7648 free (0 chunks); 544 usedSPI Plan: 3072 total in 2 blocks; 1320 free (0 chunks); 1752 usedSPI Plan: 7168 total in 3 blocks; 1312 free (0 chunks); 5856 usedSPI Plan: 3072 total in 2 blocks; 1320 free (0 chunks); 1752 usedSPI Plan: 3072 total in 2 blocks; 928 free (0 chunks); 2144 usedSPI Plan: 7168 total in 3 blocks; 3920 free (0 chunks); 3248 usedSPI Plan: 3072 total in 2 blocks; 1224 free (0 chunks); 1848 usedPL/PgSQL function context: 24576 total in 2 blocks; 12008 free (9 chunks); 12568 usedCFuncHash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 usedRendezvous variable hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 usedPLpgSQL function cache: 24224 total in 2 blocks; 3744 free (0 chunks); 20480 usedType information cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 usedS_2: 1024 total in 1 blocks; 488 free (0 chunks); 536 usedS_1: 1024 total in 1 blocks; 488 free (0 chunks); 536 usedPrepared Queries: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 usedRecord information cache: 24576 total in 2 blocks; 15984 free (5 chunks); 8592 usedMessageContext: 8192 total in 1 blocks; 7744 free (0 chunks); 448 usedOperator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 usedsmgr relation table: 57344 total in 3 blocks; 21872 free (8 chunks); 35472 usedTransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks); 32 usedPortal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 usedPortalMemory: 8192 total in 1 blocks; 7888 free (1 chunks); 304 usedPortalHeapMemory: 1024 total in 1 blocks; 768 free (0 chunks); 256 usedExecutorState: 122880 total in 4 blocks; 68728 free (2 chunks); 54152 usedHashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 usedHashBatchContext: 32888 total in 3 blocks; 14512 free (0 chunks); 18376 usedExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 usedExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 usedExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 usedExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 usedExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 usedAggContext: 1033887744 total in 133 blocks; 688 free (0 chunks); 1033887056 used **TupleHashTable: 566485040 total in 78 blocks; 2064896 free (304 chunks); 564420144 used **ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 usedExprContext: 8192 total in 1 blocks; 8040 free (1 chunks); 152 usedExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 usedRelcache by OID: 57344 total in 3 blocks; 31024 free (6 chunks); 26320 usedCacheMemoryContext: 4679720 total in 23 blocks; 404104 free (1 chunks); 4275616 used
If you have actually read this far, the following wrinkle is where I am currently stuck.
I set ENABLE_HASHAGG=OFF and still get an OOM :-(
In this situation Postgres runs for much longer before getting the OOM and I believe it has actually written some records to the aggregationI base this on seeing autovacuum kick off against the aggregation table after the query fails, which may not be valid.
userquery Scan table_4760 (cost=8771233.77..9521474.17 rows=5000 width=152) -> GroupAggregate (cost=8771233.77..9521349.17 rows=5000 width=40) -> Sort (cost=8771233.77..8864982.57 rows=37499520 width=40) Sort Key: public.customer_stats_dtl.user_id, policy.policy_group_id -> Hash Join (cost=7.81..2241002.00 rows=37499520 width=40) Hash Cond: (public.customer_stats_dtl.policy_id = policy.policy_id) -> Append (cost=0.00..1641001.87 rows=59999232 width=40) -> Seq Scan on customer_stats_dtl (cost=0.00..22.45 rows=4 width=40) Filter: ((period_start >= '2007-09-08 20:00:00-04'::timestamp with time zone) AND (period_start < '2007-09-09 08:00:00-04'::timestamp with time zone)) -> Seq Scan on customer_stats_dtl_027 customer_stats_dtl (cost=0.00..1640979.42 rows=59999228 width=40) Filter: ((period_start >= '2007-09-08 20:00:00-04'::timestamp with time zone) AND (period_start < '2007-09-09 08:00:00-04'::timestamp with time zone)) -> Hash (cost=6.25..6.25 rows=125 width=8) -> Seq Scan on policy (cost=0.00..6.25 rows=125 width=8)
TopMemoryContext: 874608 total in 106 blocks; 14240 free (45 chunks); 860368 usedunnamed prepared statement: 2097152 total in 8 blocks; 720544 free (2 chunks); 1376608 usedTopTransactionContext: 8192 total in 1 blocks; 7648 free (0 chunks); 544 usedAfterTriggerEvents: 758112256 total in 102 blocks; 1792 free (8 chunks); 758110464 usedS_2: 1024 total in 1 blocks; 488 free (0 chunks); 536 usedType information cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 usedSPI Plan: 3072 total in 2 blocks; 1320 free (0 chunks); 1752 usedSPI Plan: 7168 total in 3 blocks; 1312 free (0 chunks); 5856 usedSPI Plan: 3072 total in 2 blocks; 1320 free (0 chunks); 1752 usedSPI Plan: 3072 total in 2 blocks; 928 free (0 chunks); 2144 usedSPI Plan: 7168 total in 3 blocks; 3920 free (0 chunks); 3248 usedSPI Plan: 3072 total in 2 blocks; 1224 free (0 chunks); 1848 usedPL/PgSQL function context: 24576 total in 2 blocks; 12008 free (9 chunks); 12568 usedCFuncHash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 usedRendezvous variable hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 usedPLpgSQL function cache: 24224 total in 2 blocks; 3744 free (0 chunks); 20480 usedS_1: 1024 total in 1 blocks; 488 free (0 chunks); 536 usedPrepared Queries: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 usedRecord information cache: 24576 total in 2 blocks; 15984 free (5 chunks); 8592 usedMessageContext: 8192 total in 1 blocks; 7744 free (0 chunks); 448 usedOperator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 usedsmgr relation table: 253952 total in 5 blocks; 40912 free (16 chunks); 213040 usedTransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks); 32 usedPortal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 usedPortalMemory: 8192 total in 1 blocks; 7888 free (1 chunks); 304 usedPortalHeapMemory: 1024 total in 1 blocks; 768 free (0 chunks); 256 usedExecutorState: 122880 total in 4 blocks; 61720 free (15 chunks); 61160 usedExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 usedHashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 usedHashBatchContext: 32888 total in 3 blocks; 14512 free (0 chunks); 18376 usedTupleSort: 822437960 total in 105 blocks; 424201336 free (5267294 chunks); 398236624 usedExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 usedExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 usedExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 usedExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 usedExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 usedAggContext: 8192 total in 1 blocks; 8000 free (3 chunks); 192 usedExprContext: 8192 total in 1 blocks; 8000 free (0 chunks); 192 usedExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 usedExprContext: 8192 total in 1 blocks; 8040 free (0 chunks); 152 usedRelcache by OID: 253952 total in 5 blocks; 117488 free (8 chunks); 136464 usedCacheMemoryContext: 17262632 total in 25 blocks; 3573056 free (0 chunks); 13689576 used
_________________________________________________________________
Explore the seven wonders of the world
http://search.msn.com/results.aspx?q=7+wonders+world&mkt=en-US&form=QBRE