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

Search Discussions

  • Tom Lane at Apr 10, 2006 at 10:44 pm

    "Pazargic Antonel Ernest" <antonel.pazargic@gmail.com> writes:
    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:
    It works for me (tiny example attached). You might want to try "show
    constraint_exclusion" just to verify you turned it on correctly.

    regards, tom lane

    regression=# create table master (timpul timestamp);
    CREATE TABLE
    regression=# create table t1 (check (timpul >= '2005-01-01' and timpul < '2006-01-01')) inherits (master);
    CREATE TABLE
    regression=# create table t2 (check (timpul >= '2006-01-01' and timpul < '2007-01-01')) inherits (master);
    CREATE TABLE
    regression=# explain select * from master where timpul = '2005-10-01';
    QUERY PLAN
    -------------------------------------------------------------------------------------
    Result (cost=0.00..102.75 rows=30 width=8)
    -> Append (cost=0.00..102.75 rows=30 width=8)
    -> Seq Scan on master (cost=0.00..34.25 rows=10 width=8)
    Filter: (timpul = '2005-10-01 00:00:00'::timestamp without time zone)
    -> Seq Scan on t1 master (cost=0.00..34.25 rows=10 width=8)
    Filter: (timpul = '2005-10-01 00:00:00'::timestamp without time zone)
    -> Seq Scan on t2 master (cost=0.00..34.25 rows=10 width=8)
    Filter: (timpul = '2005-10-01 00:00:00'::timestamp without time zone)
    (8 rows)

    regression=# set constraint_exclusion to 1;
    SET
    regression=# explain select * from master where timpul = '2005-10-01';
    QUERY PLAN
    -------------------------------------------------------------------------------------
    Result (cost=0.00..68.50 rows=20 width=8)
    -> Append (cost=0.00..68.50 rows=20 width=8)
    -> Seq Scan on master (cost=0.00..34.25 rows=10 width=8)
    Filter: (timpul = '2005-10-01 00:00:00'::timestamp without time zone)
    -> Seq Scan on t1 master (cost=0.00..34.25 rows=10 width=8)
    Filter: (timpul = '2005-10-01 00:00:00'::timestamp without time zone)
    (6 rows)

    regression=#

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedApr 6, '06 at 10:55a
activeApr 10, '06 at 10:44p
posts2
users2
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase