From: Kevin Kempter <cs_dba@consistentstate.com>
To: pgsql-performance@postgresql.org
Subject: dates and partitioning
Date: Tue, 20 Jul 2010 09:36:07 -0600
Message-ID: <201007200936.07736.cs_dba@consistentstate.com> (raw)
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...
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cs_dba@consistentstate.com
Subject: Re: dates and partitioning
In-Reply-To: <201007200936.07736.cs_dba@consistentstate.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox