pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feeddates and partitioning
3+ messages / 3 participants
[nested] [flat]
* dates and partitioning
@ 2010-07-20 15:36 Kevin Kempter <cs_dba@consistentstate.com>
0 siblings, 2 replies; 3+ messages in thread
From: Kevin Kempter @ 2010-07-20 15:36 UTC (permalink / raw)
To: 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...
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: dates and partitioning
@ 2010-07-20 15:55 Brad Nicholson <bnichols@ca.afilias.info>
parent: Kevin Kempter <cs_dba@consistentstate.com>
1 sibling, 0 replies; 3+ messages in thread
From: Brad Nicholson @ 2010-07-20 15:55 UTC (permalink / raw)
To: Kevin Kempter <cs_dba@consistentstate.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: dates and partitioning
@ 2010-07-20 15:55 Jochen Erwied <jochen@pgsql-performance.erwied.eu>
parent: Kevin Kempter <cs_dba@consistentstate.com>
1 sibling, 0 replies; 3+ messages in thread
From: Jochen Erwied @ 2010-07-20 15:55 UTC (permalink / raw)
To: pgsql-performance
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: jochen@erwied.eu +49-208-38800-18, FAX: -19
Sauerbruchstr. 17 | work: joe@mbs-software.de +49-2151-7294-24, FAX: -50
D-45470 Muelheim | mobile: jochen.erwied@vodafone.de +49-173-5404164
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-07-20 15:55 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-07-20 15:36 dates and partitioning Kevin Kempter <cs_dba@consistentstate.com>
2010-07-20 15:55 ` Brad Nicholson <bnichols@ca.afilias.info>
2010-07-20 15:55 ` Jochen Erwied <jochen@pgsql-performance.erwied.eu>
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