From: | "Huang, Suya" <Suya(dot)Huang(at)au(dot)experian(dot)com> |
---|---|
To: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | query on parent partition table has bad performance |
Date: | 2014-08-20 09:30:27 |
Message-ID: | D83E55F5F4D99B4A9B4C4E259E6227CD014F684C@AUX1EXC02.apac.experian.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi,
I have a question about partition table query performance in postgresql, it's an old version 8.3.21, I know it's already out of support. so any words about the reason for the behavior would be very much appreciated.
I have a partition table which name is test_rank_2014_monthly and it has 7 partitions inherited from the parent table, each month with one partition. The weird thing is query out of the parent partition is as slow as query from a non-partitioned table, however, query from child table directly is really fast.
have no idea... is this an expected behavior of partition table in old releases?
hitwise_uk=# explain analyze select * from test_rank_2014_07 r WHERE r.date = 201407 ;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Seq Scan on test_rank_2014_07 r (cost=0.00..169797.75 rows=7444220 width=54) (actual time=0.007..1284.622 rows=7444220 loops=1)
Filter: (date = 201407)
Total runtime: 1831.379 ms
(3 rows)
-- query on parent table
hitwise_uk=# explain analyze select * from test_rank_2014_monthly r WHERE r.date = 201407 ;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..169819.88 rows=7444225 width=54) (actual time=0.009..4484.552 rows=7444220 loops=1)
-> Append (cost=0.00..169819.88 rows=7444225 width=54) (actual time=0.008..2495.457 rows=7444220 loops=1)
-> Seq Scan on test_rank_2014_monthly r (cost=0.00..22.12 rows=5 width=54) (actual time=0.000..0.000 rows=0 loops=1)
Filter: (date = 201407)
-> Seq Scan on test_rank_2014_07 r (cost=0.00..169797.75 rows=7444220 width=54) (actual time=0.007..1406.600 rows=7444220 loops=1)
Filter: (date = 201407)
Total runtime: 5036.092 ms
(7 rows)
--query on non-partitioned table
hitwise_uk=# explain analyze select * from rank_2014_monthly r WHERE r.date = 201407 ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on rank_2014_monthly r (cost=0.00..1042968.85 rows=7424587 width=54) (actual time=3226.983..4537.974 rows=7444220 loops=1)
Filter: (date = 201407)
Total runtime: 5086.096 ms
(3 rows)
check constraints on child table is something like below:
...
Check constraints:
"test_rank_2014_07_date_check" CHECK (date = 201407)
Inherits: test_rank_2014_monthly
Thanks,
Suya
From | Date | Subject | |
---|---|---|---|
Next Message | David G Johnston | 2014-08-20 13:49:38 | Re: query on parent partition table has bad performance |
Previous Message | Kevin Grittner | 2014-08-19 18:43:30 | Re: query against pg_locks leads to large memory alloc |