Thread: Re: Partitioning for query performance
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.
On 2010-04-13, Danny Lo <lo.dannyk@gmail.com> wrote: > This is a multi-part message in MIME format. > > ------=_NextPart_000_004E_01CADB39.66239FF0 > Content-Type: text/plain; > charset="us-ascii" > Content-Transfer-Encoding: 7bit > > Hi, > > > > I have a question regarding improving query performing using table > partitioning: I recently partitioned a 20Gb table and was amazed at the performance boost. I only did it because I wanted to delete old data and the original table was too busy to vacuum. > EXECUTE 'SELECT * from > tablename > > where date = ''' || > current_date || ''''; > > .... > 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. (PLPGSQL) EXECUTE is not a parameterised query. The way you are doing it EXECUTE sees a string with only constants in it. In any case even without constraint exclusion you can get good (but not best) perfromance if your parts are indexed on the relevant column; as an index scan which discovers the requested value is too high or is too low is very fast (not as fast as constraint exclusion, but still fast)