Skip site navigation (1) Skip section navigation (2)

Re: Partitioning for query performance

From: "Danny Lo" <lo(dot)dannyk(at)gmail(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Partitioning for query performance
Date: 2010-04-13 08:44:39
Message-ID: 4bc42eff.1508c00a.1de5.6034@mx.google.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi, 

 

I have a question regarding improving query performing using table
partitioning:

 

I am a postgreSQL novice and have a table with  1-1.5TB of data. I am
thinking about a range partition of the data using date ranges as my queries
often condition on a date or range of dates in the WHERE clause. However, I
often run my queries dynamically in a pl/pgsql function using the EXECUTE
command. It goes something like this:

 

                              WHILE (current_date <= end_date) LOOP

                                             

                                             /* some code */

 

                                             EXECUTE 'SELECT * from
tablename

                                             where date = ''' ||
current_date || '''';

                                             

                                             /* some more code */

                                             current_date := current_date +
1;

                              END LOOP;

 

 

In light of the below caveat from the postgreSQL manual, am I right to say
there is no performance benefit from table partitioning?

Thanks very much!

The following caveats apply to constraint exclusion: 

.        Constraint exclusion only works when the query's WHERE clause
contains constants. A parameterized query will not be optimized, since the
planner cannot know which partitions the parameter value might select at run
time. For the same reason, "stable" functions such as CURRENT_DATE must be
avoided. 

 

Responses

pgsql-novice by date

Next:From: Machiel RichardsDate: 2010-04-13 10:03:26
Subject: Autvacuum and Analyze stats?
Previous:From: Syan TanDate: 2010-04-13 02:31:33
Subject: Re: Postgres 8.4 view keys

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group