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
