Constraint exclusion in partitions - Mailing list pgsql-novice

From Daniel Begin
Subject Constraint exclusion in partitions
Date
Msg-id COL129-DS2220BED40F2E660B70F98194C00@phx.gbl
Whole thread Raw
List 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

pgsql-novice by date:

Previous
From: Daniel Begin
Date:
Subject: Constraint exclusion in partitions
Next
From: "Hans Ginzel"
Date:
Subject: psql readline Tab insert tab