Constraint exclusion in partitions

From: Daniel Begin <jfd553(at)hotmail(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Constraint exclusion in partitions
Date: 2015-05-21 20:51:40
Message-ID: COL129-DS72199ADB4C725F41A487A94C10@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi all,

I have split a large table (billions of records) into multiple partitions,
hoping the access would be faster. I used an ID to make partitions check
(check (id >= 100 AND id < 200).) and created over 80 tables (children) that
are now filled with data.

However, after I did it, I read a second time the following sentence in the
documentation and started wondering what it actually means .

"Constraint exclusion only works when the query's WHERE clause contains
constants (or externally supplied parameters)"

It is clear that the following query will use constraint exclusion and will
run faster.

a- Select * from parent_table where id >=9999; -- using a constant

But how the following queries would react.

b- Select * from parent_table where id between 2345 and 6789; -- using
a range of ids

c- Select * from parent_table where id in(select ids from
anothertable); -- using a list of ids from a select

Since I mostly use queries of type b and c, I am wondering if partitioning
the large table was appropriate.

Thank in advance

Daniel

Doc: http://www.postgresql.org/docs/9.3/static/ddl-partitioning.html

Browse pgsql-novice by date

  From Date Subject
Next Message Daniel Begin 2015-05-22 10:36:47 Constraint exclusion in partitions
Previous Message Andreas Kretschmer 2015-05-19 06:23:51 Re: Help with SQL updating not working.