[PERFORM] dates and partitioning

[Kevin Kempter]

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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] dates and partitioning

[Brad Nicholson]

On Tue, 2010-07-20 at 09:36 -0600, Kevin Kempter wrote:
 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?

That's E is an escape character.  Python is likely putting that in.

See http://www.postgresql.org/docs/8.4/static/sql-syntax-lexical.html -
section 4.1.2.2

 
 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?

Do you have constraint_exclusion turned on?

-- 
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] dates and partitioning

[Jochen Erwied]

Tuesday, July 20, 2010, 5:36:07 PM you wrote:


 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?

Quick guess: How is your 'constraint_exclusion'-setting? 
Which version of postgres?

-- 
Jochen Erwied |   home: joc...@erwied.eu +49-208-38800-18, FAX: -19
Sauerbruchstr. 17 |   work: j...@mbs-software.de  +49-2151-7294-24, FAX: -50
D-45470 Muelheim  | mobile: jochen.erw...@vodafone.de   +49-173-5404164


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance