pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Milen Kulev <makulev@gmx.net>
To: pgsql-performance@postgresql.org
Subject: Partition elimination problem
Date: Mon, 18 Sep 2006 00:12:33 +0200
Message-ID: <002201c6daa6$6057a220$0a00a8c0@trivadis.com> (raw )
Hi listers,
I wanted to try PG partitioning (aka constraint exclusion) with two levels .
I am using PG 8.1.3 on RHEL4U2,
My setup:
CREATE TABLE part (
id1 int not null,
id2 int not null,
id3 int not null,
filler varchar(200)
);
--- level 1 partitions on id1 column only
create table part_id1_0_10 ( CHECK ( id1>= 0 and id1<=10) ) INHERITS (part);
create table part_id1_11_20 ( CHECK ( id1>=11 and id1<=20) ) INHERITS (part);
--- level2 partitions
-- subpartitions for parent partition1
create table part_id1_0_10__id2_0_10 ( CHECK ( id2>= 0 and id2<=10) ) INHERITS(part_id1_0_10);
create table part_id1_0_10__id2_11_20 ( CHECK ( id2>= 11 and id2<=20) ) INHERITS(part_id1_0_10);
-- subpartitions for parent partition2
create table part_id1_11_20__id2_0_10 ( CHECK ( id2>= 0 and id2<=10) ) INHERITS(part_id1_11_20);
create table part_id1_11_20__id2_11_20 ( CHECK ( id2>= 11 and id2<=20) ) INHERITS(part_id1_11_20);
I have created indexes on all tables.
My Problem is that I don't see partiotion elimination feature (Parameer constraint_exclusion is ON):
pgpool=# EXPLAIN ANALYZE select * from part where id1 = 3 and id2 = 5;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
-----------------------------
Result (cost=0.00..957.04 rows=5 width=130) (actual time=1.606..9.216 rows=483 loops=1)
-> Append (cost=0.00..957.04 rows=5 width=130) (actual time=1.602..7.910 rows=483 loops=1)
-> Seq Scan on part (cost=0.00..24.85 rows=1 width=130) (actual time=0.001..0.001 rows=0 loops=1)
Filter: ((id1 = 3) AND (id2 = 5))
-> Bitmap Heap Scan on part_id1_0_10 part (cost=1.02..9.50 rows=1 width=130) (actual time=0.014..0.014 rows=0
loops=1)
Recheck Cond: (id1 = 3)
Filter: (id2 = 5)
-> Bitmap Index Scan on idx_part_id1_0_10 (cost=0.00..1.02 rows=5 width=0) (actual time=0.010..0.010
rows=0 loops=1)
Index Cond: (id1 = 3)
-> Bitmap Heap Scan on part_id1_11_20 part (cost=2.89..436.30 rows=1 width=130) (actual time=0.025..0.025
rows=0 loops=1)
Recheck Cond: (id1 = 3)
Filter: (id2 = 5)
-> Bitmap Index Scan on idx_part_id1_11_20 (cost=0.00..2.89 rows=254 width=0) (actual time=0.021..0.021
rows=0 loops=1)
Index Cond: (id1 = 3)
-> Bitmap Heap Scan on part_id1_0_10__id2_0_10 part (cost=2.52..255.56 rows=1 width=130) (actual
time=1.554..6.526 rows=483 loops=1)
Recheck Cond: (id2 = 5)
Filter: (id1 = 3)
-> Bitmap Index Scan on idx_part_id1_0_10__id2_0_10 (cost=0.00..2.52 rows=148 width=0) (actual
time=1.410..1.410 rows=5242 loops=1)
Index Cond: (id2 = 5)
-> Bitmap Heap Scan on part_id1_0_10__id2_11_20 part (cost=2.47..230.82 rows=1 width=130) (actual
time=0.034..0.034 rows=0 loops=1)
Recheck Cond: (id2 = 5)
Filter: (id1 = 3)
-> Bitmap Index Scan on idx_part_id1_0_10__id2_11_20 (cost=0.00..2.47 rows=134 width=0) (actual
time=0.030..0.030 rows=0 loops=1)
Index Cond: (id2 = 5)
Total runtime: 9.950 ms
(25 rows)
Why PG is searching in part_id1_11_20 table, for example ? From the check contraint it is pretty
Clear that in this table there are not records with ids =3 ??
pgpool=# \d+ part_id1_11_20
Table "public.part_id1_11_20"
Column | Type | Modifiers | Description
--------+------------------------+-----------+-------------
id1 | integer | not null |
id2 | integer | not null |
id3 | integer | not null |
filler | character varying(200) | |
Indexes:
"idx_part_id1_11_20" btree (id1)
Check constraints:
"part_id1_11_20_id1_check" CHECK (id1 >= 11 AND id1 <= 20)
Inherits: part
Has OIDs: no
Best Regards.
Milen
view thread (3+ messages) latest in thread
Message-ID: <002201c6daa6$6057a220$0a00a8c0@trivadis.com>
Permalink: ../002201c6daa6$6057a220$0a00a8c0@trivadis.com/
Also on: postgresql.org/message-id/002201c6daa6$6057a220$0a00a8c0@trivadis.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: makulev@gmx.net
Subject: Re: Partition elimination problem
In-Reply-To: <002201c6daa6$6057a220$0a00a8c0@trivadis.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox