pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Neil Peter Braggio <pbraggio@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Query performance problems with partitioned tables
Date: Mon, 30 Apr 2007 10:06:07 -0400
Message-ID: <a722ba580704300706o24947e13id5b355684a5e0ff@mail.gmail.com> (raw)
In-Reply-To: <4635F2E1.3090602@archonet.com>
References: <4635D678.3050205@xss.co.at>
	<87ps5mgghp.fsf@meuh.mnc.lan>
	<4635EF3A.1070204@xss.co.at>
	<4635F2E1.3090602@archonet.com>

Just cast the value in the WHERE clause:

select ts from mwdb.t_mv where zr=3622 and ts > '2006-01-01 00:00:00'
::TIMESTAMP order by ts asc limit 1;

This search only into the right partitioned tables if you build the rules
based in the ts field.


----
Neil Peter Braggio
pbraggio@gmail.com


On 4/30/07, Richard Huxton <dev@archonet.com> wrote:
>
> Andreas Haumer wrote:
> >
> > I think the planner could do the following:
> >
> > a) It could make a better decision in which direction to scan
> >    the partitions (depending on sort order involved in the query)
> >
> > b) It could stop scanning as soon as there can not be any further
> >    resulting row according to the CHECK constraints given on the tables.
> [snip]
> > Perhaps the logic to implement this is complex, but IMHO
> > it _should_ be doable (and proofable), shouldn't it?
>
> Ah, it might be do-able for some subset of cases, but is it
> cost-effective to check for in *all* cases? Don't forget the constraints
> and where clauses can be arbitrarily complex.
> --
>    Richard Huxton
>    Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>

view thread (18+ messages)  latest in thread

Message-ID: <a722ba580704300706o24947e13id5b355684a5e0ff@mail.gmail.com>
Permalink:  ../a722ba580704300706o24947e13id5b355684a5e0ff@mail.gmail.com/
Also on:    postgresql.org/message-id/a722ba580704300706o24947e13id5b355684a5e0ff@mail.gmail.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: pbraggio@gmail.com
  Subject: Re: Query performance problems with partitioned tables
  In-Reply-To: <a722ba580704300706o24947e13id5b355684a5e0ff@mail.gmail.com>
  References: <4635D678.3050205@xss.co.at> <87ps5mgghp.fsf@meuh.mnc.lan> <4635EF3A.1070204@xss.co.at> <4635F2E1.3090602@archonet.com> <a722ba580704300706o24947e13id5b355684a5e0ff@mail.gmail.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