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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
