Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 8E8599FB59B for ; Mon, 30 Apr 2007 11:06:13 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 36871-02 for ; Mon, 30 Apr 2007 11:06:04 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by postgresql.org (Postfix) with ESMTP id BDAA79FB226 for ; Mon, 30 Apr 2007 11:06:09 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id k3so965215ugf for ; Mon, 30 Apr 2007 07:06:07 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=ZwPqc6MguQeLCf+1B48Tw0heWS5RFifJBSNQN6mFP7f1fj4ZFe7M+IIo2iQBTJVMENY9cJZHZ7w3CmzxCVgDedaG8PoH/iC1ny1AymwkAXE07tnwqL+oaN4Lr7Et+yaqegXPyh560YlcKlOJ7WIGxfUBo/dzenXOJAaJK0Wb67M= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=LiI8je03iwwoCUkTfesovE70sq8PRaInMitRLaDsuHDBnjICiuaj4MbqOdKAtMHwcLtuM5oFO0fC8WW/67usxe9EwlpM94PGaBANGNgct+Q+TtGXRkOws4IS/JZOIKaAlZHt3UKSePJISKruYFD3/m5A2/ytn+JmXLcNdLwH6io= Received: by 10.82.191.3 with SMTP id o3mr12042697buf.1177941967282; Mon, 30 Apr 2007 07:06:07 -0700 (PDT) Received: by 10.82.107.19 with HTTP; Mon, 30 Apr 2007 07:06:07 -0700 (PDT) Message-ID: Date: Mon, 30 Apr 2007 10:06:07 -0400 From: "Neil Peter Braggio" To: pgsql-performance@postgresql.org Subject: Re: Query performance problems with partitioned tables In-Reply-To: <4635F2E1.3090602@archonet.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_49778_9272418.1177941967014" References: <4635D678.3050205@xss.co.at> <87ps5mgghp.fsf@meuh.mnc.lan> <4635EF3A.1070204@xss.co.at> <4635F2E1.3090602@archonet.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/504 X-Sequence-Number: 24381 ------=_Part_49778_9272418.1177941967014 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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 > ------=_Part_49778_9272418.1177941967014 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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

------=_Part_49778_9272418.1177941967014--