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

dates and partitioning

From: Kevin Kempter <cs_dba(at)consistentstate(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: dates and partitioning
Date: 2010-07-20 15:36:07
Message-ID: 201007200936.07736.cs_dba@consistentstate.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi All;

we have a table partitioned by day, the check constraint on the child tables 
looks like this (this is the may 31st partition):

CHECK 
(stime >= '2010-05-30 00:00:00+00'::timestamp with time zone 
  AND stime <= '2010-05-30 23:59:59+00'::timestamp with time zone)


We have a python based app that creates code like this:

                select
                 *
                from
                    table_a a, 
                    table_b b,
                    table_d d
                where a.id = b.id
                    and b.id = d.id
                    and stime >= timestamp %s at time zone \'UTC\'
                    and stime < timestamp %s at time zone \'UTC\'
                    and stime >= timestamp %s at time zone d.name
                    and stime < timestamp %s at time zone d.name
                   ...


so here's my questions:

1) the above app generated query pshows up like this in pg_stat_activity:

and stime >= timestamp E'2010-07-17' at time zone 'UTC'   
and stime < timestamp E'2010-07-21' at time zone 'UTC' 
and stime >= timestamp E'2010-07-18' at time zone d.name  
and stime < timestamp E'2010-07-19' at time zone d.name 

what's the E'date' from? and why does it show up this way?


2) the above query creates a plan that does a sequential scan & filter on 
every partition. Why won't it only hit the correct partitions? Is it due to 
the way the date was specified? or maybe the "at time zone" syntax?


Thanks in advance...

Responses

pgsql-performance by date

Next:From: Brad NicholsonDate: 2010-07-20 15:55:30
Subject: Re: dates and partitioning
Previous:From: Daniel Ferreira de LimaDate: 2010-07-19 19:53:37
Subject: Re: IDE x SAS RAID 0 on HP DL 380 G5 P400i controller performance problem

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