Explain query on table with partition tables

Поиск
Список
Период
Сортировка
От Pazargic Antonel Ernest
Тема Explain query on table with partition tables
Дата
Msg-id op.s7k1yeq09hcmn4@localhost.localdomain
обсуждение исходный текст
Ответы Re: Explain query on table with partition tables
Список 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)
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

В списке pgsql-novice по дате отправления:

Предыдущее
От: James Long
Дата:
Сообщение: How to append records into a file which has serial unique IDs?
Следующее
От: "Pazargic Ernest Antonel"
Дата:
Сообщение: Explain query on table with partition tables is strange