FAQ
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.

Search Discussions

  • Jasen Betts at Apr 13, 2010 at 12:59 pm

    On 2010-04-13, Danny Lo 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)

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedApr 13, '10 at 8:45a
activeApr 13, '10 at 12:59p
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Jasen Betts: 1 post Danny Lo: 1 post

People

Translate

site design / logo © 2022 Grokbase