Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 15BC5632EFE for ; Sat, 17 Apr 2010 01:12:31 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 48760-02 for ; Sat, 17 Apr 2010 04:12:19 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ww0-f46.google.com (mail-ww0-f46.google.com [74.125.82.46]) by mail.postgresql.org (Postfix) with ESMTP id 209B0632A26 for ; Sat, 17 Apr 2010 01:12:19 -0300 (ADT) Received: by wwb24 with SMTP id 24so1562615wwb.19 for ; Fri, 16 Apr 2010 21:12:19 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:received:message-id:subject:from:to:cc:content-type; bh=sAlZwrWLuLy/cxvsQ8SMhTYwls/okneXXU88HlSZA3M=; b=u8cwwVM9Uoo9HguiVubLKqDqQtPtqGys7dMA12VTS6Hxbakv+jX0H3DnFTwvos0v/p 37v1TFMTwCGvbzn3UylAHkmq+FBStdXprb28GpaWf+xqksbSN7J2ppHzyzHM43HxYJMz GHmOmQR0BI0oCWB0AQojQPfuqrj4s5yFdq7SI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=mx39FBq6+AU/XAV99K+eLAVO+KvAowX3iNEKgZ8OX487uaQJo725JUGgPrG7NvshVs bmaHxwPegfMWLLtdQmUbf6Nn39mU/m9NYE4jPyZ27PZ2hCm21cFgPXKSJSdhs4pE4kgj Xsxh42FjEGjL8H/f2ZQmMlRWSG5SCorR7QrNI= MIME-Version: 1.0 Received: by 10.216.25.194 with HTTP; Fri, 16 Apr 2010 21:12:18 -0700 (PDT) In-Reply-To: <8534.1271427587@sss.pgh.pa.us> References: <8534.1271427587@sss.pgh.pa.us> Date: Sat, 17 Apr 2010 07:12:18 +0300 Received: by 10.216.86.85 with SMTP id v63mr3240013wee.32.1271477538948; Fri, 16 Apr 2010 21:12:18 -0700 (PDT) Message-ID: Subject: Re: Planner not using column limit specified for one column for another column equal to first From: =?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= To: Tom Lane Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=0016e6d9a0570c881d048466efeb X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.132 tagged_above=-10 required=5 tests=AWL=0.767, BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201004/227 X-Sequence-Number: 38477 --0016e6d9a0570c881d048466efeb Content-Type: text/plain; charset=KOI8-U Content-Transfer-Encoding: quoted-printable 16 =CB=D7=A6=D4=CE=D1 2010 =D2. 17:19 Tom Lane =CE=C1= =D0=C9=D3=C1=D7: > =3D?KOI8-U?B?96bUwcymyiD0yc3eydvJzg=3D=3D?=3D writes: > > I've thought and someone in this list've told me that this should be do= ne > > automatically. > > As was pointed out, even if we had such logic it wouldn't apply in this > example, because the equality conditions aren't real equalities but > OUTER JOIN conditions. > > In this case you can copy condition to "ON" condition, not to where cause and this would work correct, e.g. "select something from a join b on a.x=3D= b.y where a.x > n" <=3D> "select something from a join b on a.x=3Db.y and b.y >= n where a.x > n". As of making planner more clever, may be it is possible to introduce division on "fast queries" and "long queries", so that if after fast planning cost is greater then some configurable threshold, advanced plannin= g techniques (or settings) are used. As far as I have seen in this list, many techniques are not used simply because they are too complex and could make planning take too much time for really fast queries, but they are vital for long ones. Also same (or similar) threshold could be used to enable replanning for eac= h run of prepared query - also an often complaint is that planned query is no= t that fast as is could be. --=20 Best regards, Vitalii Tymchyshyn --0016e6d9a0570c881d048466efeb Content-Type: text/html; charset=KOI8-U Content-Transfer-Encoding: quoted-printable

16 =CB=D7=A6=D4=CE=D1 2010 =D2. 17:19 To= m Lane <tgl@sss.p= gh.pa.us> =CE=C1=D0=C9=D3=C1=D7:
=3D?KOI8-U?B?96bUwcymyiD0yc3eydvJzg=3D=3D?=3D <tivv00@gmail.com> writes:
> I've thought and someone in this list've told me that this sho= uld be done
> automatically.

As was pointed out, even if we had such logic it wouldn't apply in this=
example, because the equality conditions aren't real equalities but
OUTER JOIN conditions.


In this case= you can copy=9Acondition=9Ato "ON" condition, not to where cause= and this would work correct, e.g. "select something from a join b on = a.x=3Db.y where a.x > n" <=3D> "select something from a = join b on a.x=3Db.y and b.y > n where a.x > n".

As of making planner more clever, may be it is possible= to introduce division on "fast queries" and "long queries&q= uot;, so that if after fast planning cost is greater then some configurable= threshold, advanced planning techniques (or settings) are used. As far as = I have seen in this list, many techniques are not used simply because they = are too complex and could make planning take too much time for really fast = queries, but they are vital for long ones.
Also same (or similar) threshold could be used to enable replanning fo= r each run of prepared query - also an often complaint is that planned quer= y is not that fast as is could be.
=9A
--
Best reg= ards,
Vitalii Tymchyshyn
--0016e6d9a0570c881d048466efeb--