Explain query on table with partition tables is strange - Mailing list pgsql-novice
From | Pazargic Ernest Antonel |
---|---|
Subject | Explain query on table with partition tables is strange |
Date | |
Msg-id | 6bd9e6790604052306o14956f3by12b163046a7be49e@mail.gmail.com Whole thread Raw |
List | pgsql-novice |
I've made a master table named master and twelve partitions tables. I've made all constraint on column "timpul" in partition tables and all neccesary rules on insert operation. I've comment out constraint_exclusion and put true for that variable into postgresql.conf. I've restarted server.
I run EXPLAIN PLAN for SELECT * FROM MASTER WHERE timpul = '2006-01-01' and doesn't look that scan only one corect partition table (as I read from docs). It looks like:
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Result (cost=0.00..100.50 rows=43 width=80)
-> Append (cost=0.00..100.50 rows=43 width=80)
-> Seq Scan on master (cost= 0.00..18.88 rows=4 width=80)
Filter: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on master_012006 master (cost=0.00..1.01 rows=1 width=23)
Filter: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Heap Scan on master_022006 master (cost=2.01..9.62 rows=4 width=80)
Recheck Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on idx1_master_022006__timpul (cost= 0.00..2.01 rows=4 width=0)
Index Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Heap Scan on master_032006 master (cost=1.01..8.62 rows=4 width=80)
Recheck Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on idx1_master_032006__timpul (cost=0.00..1.01 rows=4 width=0)
Index Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on master_042006 master (cost=0.00..1.00 rows=1 width=80)
Filter: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Heap Scan on master_052006 master (cost= 1.01..8.62 rows=4 width=80)
Recheck Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on idx1_master_052006__timpul (cost=0.00..1.01 rows=4 width=0)
Index Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Heap Scan on master_062006 master (cost=1.01..8.62 rows=4 width=80)
Recheck Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on idx1_master_062006__timpul (cost=0.00..1.01 rows=4 width=0)
Index Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Heap Scan on master_072006 master (cost= 1.01..8.62 rows=4 width=80)
Recheck Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on idx1_master_072006__timpul (cost=0.00..1.01 rows=4 width=0)
Index Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Heap Scan on master_082006 master (cost=1.01..8.62 rows=4 width=80)
Recheck Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on idx1_master_082006__timpul (cost=0.00..1.01 rows=4 width=0)
Index Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Heap Scan on master_092006 master (cost= 1.01..8.62 rows=4 width=80)
Recheck Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on idx1_master_092006__timpul (cost=0.00..1.01 rows=4 width=0)
Index Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Heap Scan on master_102006 master (cost=1.01..8.62 rows=4 width=80)
Recheck Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on idx1_master_102006__timpul (cost=0.00..1.01 rows=4 width=0)
Index Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Heap Scan on master_112006 master (cost= 1.01..8.62 rows=4 width=80)
Recheck Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on idx1_master_112006__timpul (cost=0.00..1.01 rows=4 width=0)
Index Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on master_122006 master (cost=0.00..1.01 rows=1 width=23)
Filter: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
(46 rows)
--
I run EXPLAIN PLAN for SELECT * FROM MASTER WHERE timpul = '2006-01-01' and doesn't look that scan only one corect partition table (as I read from docs). It looks like:
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Result (cost=0.00..100.50 rows=43 width=80)
-> Append (cost=0.00..100.50 rows=43 width=80)
-> Seq Scan on master (cost= 0.00..18.88 rows=4 width=80)
Filter: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on master_012006 master (cost=0.00..1.01 rows=1 width=23)
Filter: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Heap Scan on master_022006 master (cost=2.01..9.62 rows=4 width=80)
Recheck Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on idx1_master_022006__timpul (cost= 0.00..2.01 rows=4 width=0)
Index Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Heap Scan on master_032006 master (cost=1.01..8.62 rows=4 width=80)
Recheck Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on idx1_master_032006__timpul (cost=0.00..1.01 rows=4 width=0)
Index Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on master_042006 master (cost=0.00..1.00 rows=1 width=80)
Filter: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Heap Scan on master_052006 master (cost= 1.01..8.62 rows=4 width=80)
Recheck Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on idx1_master_052006__timpul (cost=0.00..1.01 rows=4 width=0)
Index Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Heap Scan on master_062006 master (cost=1.01..8.62 rows=4 width=80)
Recheck Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on idx1_master_062006__timpul (cost=0.00..1.01 rows=4 width=0)
Index Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Heap Scan on master_072006 master (cost= 1.01..8.62 rows=4 width=80)
Recheck Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on idx1_master_072006__timpul (cost=0.00..1.01 rows=4 width=0)
Index Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Heap Scan on master_082006 master (cost=1.01..8.62 rows=4 width=80)
Recheck Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on idx1_master_082006__timpul (cost=0.00..1.01 rows=4 width=0)
Index Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Heap Scan on master_092006 master (cost= 1.01..8.62 rows=4 width=80)
Recheck Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on idx1_master_092006__timpul (cost=0.00..1.01 rows=4 width=0)
Index Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Heap Scan on master_102006 master (cost=1.01..8.62 rows=4 width=80)
Recheck Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on idx1_master_102006__timpul (cost=0.00..1.01 rows=4 width=0)
Index Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Heap Scan on master_112006 master (cost= 1.01..8.62 rows=4 width=80)
Recheck Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on idx1_master_112006__timpul (cost=0.00..1.01 rows=4 width=0)
Index Cond: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
-> Seq Scan on master_122006 master (cost=0.00..1.01 rows=1 width=23)
Filter: (timpul = '2006-01-01 00:00:00'::timestamp without time zone)
(46 rows)
--
pgsql-novice by date: