|
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 |
