|
Hello, could you help me with joined query from partitioned table, please? I have a table "data" with partitions by period_id CREATE TABLE data ( period_id smallint NOT NULL DEFAULT 0, store_id smallint NOT NULL DEFAULT 0, product_id integer NOT NULL DEFAULT 0, s_pcs real NOT NULL DEFAULT 0, s_val real NOT NULL DEFAULT 0 ) CONSTRAINT data_561_period_id_check CHECK (period_id = 561) CONSTRAINT data_562_period_id_check CHECK (period_id = 562) ... When I run a simple query with a condition period_id = something I get best query plan: explain select sum(s_pcs),sum(s_val) from data d inner join periods p on d.period_id=p.period_id where p.period_id=694; QUERY PLAN ---------------------------------------------------------------------------------------- Aggregate (cost=214028.71..214028.72 rows=1 width=8) -> Nested Loop (cost=0.00..181511.71 rows=6503400 width=8) -> Index Scan using pk_periods on periods p (cost=0.00..8.27 rows=1 width=2) Index Cond: (period_id = 694) -> Append (cost=0.00..116469.44 rows=6503400 width=10) -> Seq Scan on data_694 d (cost=0.00..116446.44 rows=6503395 width=10) Filter: (d.period_id = 694) (8 rows) but when I try make a condition by join table, the query plan is not optimal: select period_id from periods where y=2009 and w=14; period_id ----------- 704 (1 row) explain select sum(s_pcs),sum(s_val) from data d inner join periods p on d.period_id=p.period_id where p.y=2009 and p.w=14; QUERY PLAN ---------------------------------------------------------------------------------------- Aggregate (cost=15313300.27..15313300.28 rows=1 width=8) -> Hash Join (cost=8.92..15293392.89 rows=3981476 width=8) Hash Cond: (d.period_id = p.period_id) -> Append (cost=0.00..12267462.15 rows=796295215 width=10) -> Seq Scan on data d (cost=0.00..20.40 rows=1040 width=10) -> Seq Scan on data_561 d (cost=0.00..66903.25 rows=4342825 width=10) -> Seq Scan on data_562 d (cost=0.00..73481.02 rows=4769802 width=10) -> Seq Scan on data_563 d (cost=0.00..73710.95 rows=4784695 width=10) -> Seq Scan on data_564 d (cost=0.00..71869.75 rows=4665175 width=10) -> Seq Scan on data_565 d (cost=0.00..72850.37 rows=4728837 width=10) ... I get same result with constraint_exclusion = partition and constraint_exclusion = on. Do you have any idea where can be a problem? For simple query the partitions works perfect on this table (about 2*10^9 records) but the joined query is an problem. Thank you very much, Vrata |
