From ilia@obnovlenie.ru Sun Aug 28 20:59:42 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3C06DD809C for ; Sun, 28 Aug 2005 17:59:42 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 88311-05 for ; Sun, 28 Aug 2005 20:59:40 +0000 (GMT) Received: from Apachihuilliztli.mtu.ru (apachihuilliztli.mtu.ru [195.34.32.124]) by svr1.postgresql.org (Postfix) with ESMTP id A8059D7F54 for ; Sun, 28 Aug 2005 17:59:38 -0300 (ADT) Received: from umail.ru (umail.mtu.ru [195.34.32.101]) by Apachihuilliztli.mtu.ru (Postfix) with ESMTP id 5ECC520A61B for ; Mon, 29 Aug 2005 00:59:40 +0400 (MSD) (envelope-from ilia@obnovlenie.ru) Received: from [85.140.125.21] (HELO ilia) by umail.ru (CommuniGate Pro SMTP 4.2b6) with ESMTP id 558493797 for pgsql-performance@postgresql.org; Mon, 29 Aug 2005 00:59:39 +0400 From: "Ilia Kantor" To: Subject: Planner improvement suggestion Date: Mon, 29 Aug 2005 00:59:33 +0400 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0022_01C5AC34.EB419DB0" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Thread-Index: AcWsE2QHh/PeY0pPRG29Y8huSDcQOw== Message-ID: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.135 required=5 tests=[AWL=0.084, FORGED_RCVD_HELO=0.05, HTML_MESSAGE=0.001] X-Spam-Level: X-Archive-Number: 200508/489 X-Sequence-Number: 14236 This is a multi-part message in MIME format. ------=_NextPart_000_0022_01C5AC34.EB419DB0 Content-Type: text/plain; charset="koi8-r" Content-Transfer-Encoding: 7bit I have a query: SELECT oh.id FROM objects_hier oh where oh.id < 2000 (!) and oh.id in ( SELECT id as id FROM objects_access oa WHERE oa.master IN (1,2,10001) AND oa.id < 2000 (!) ) The sense of the query is simple: I choose ids from objects_hier where access has necessary masters. The problem is: I have duplicate conditions for id here. They are marked with '!'. I just can't remove any of them, because planner needs to estimate both outer and inner selects to calculate the order Of nested loop or choose a join. If I remove one of duplicate conditions - planner can't estimate well. It's obvious that condition on oh.id can be put inside or outside "oh.id in ( .. )" statement with same result. So I just suggest that the planner should take this into account and "propagate" the condition outside or inside for planning if needed. P.S Is there a way to fix this particular query? Usually oh.id condition is not like <2000, but an inner join. ------=_NextPart_000_0022_01C5AC34.EB419DB0 Content-Type: text/html; charset="koi8-r" Content-Transfer-Encoding: quoted-printable

I have a query:

SELECT =
oh.id

FROM =
objects_hier oh

where

oh.id =
< 2000 (!)

and

oh.id =
in (

=9A=9A=9A=9A=9A SELECT id as id FROM =
objects_access oa

=9A=9A=9A=9A=9A =
WHERE

=9A=9A=9A=9A=9A oa.master IN =
(1,2,10001)

=9A=9A=9A=9A=9A =
AND

=9A=9A=9A=9A=9A oa.id < 2000 =
(!)

)

The sense of the query is simple: I choose = ids from objects_hier where access has necessary = masters.

The problem is: I have duplicate conditions = for id here. They are marked with ‘!’.

I just can’t remove any of them, = because planner needs to estimate both outer and inner selects to calculate the = order

Of nested loop or choose a join. If I remove = one of duplicate conditions – planner can’t estimate = well.

It’s obvious that condition on oh.id = can be put inside or outside “oh.id in ( .. )” statement with same = result.

So I just suggest that the planner should = take this into account and “propagate” the condition outside or inside = for planning if needed.

P.S

Is there a way to fix this particular query? = Usually oh.id condition is not like <2000, but an inner = join.

------=_NextPart_000_0022_01C5AC34.EB419DB0-- From tgl@sss.pgh.pa.us Sun Aug 28 21:30:51 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 550B8D8097 for ; Sun, 28 Aug 2005 18:30:51 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 94778-06 for ; Sun, 28 Aug 2005 21:30:50 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 9A0B7D809A for ; Sun, 28 Aug 2005 18:30:49 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j7SLUmHF026928; Sun, 28 Aug 2005 17:30:50 -0400 (EDT) To: "Ilia Kantor" Cc: pgsql-performance@postgresql.org Subject: Re: Planner improvement suggestion In-reply-to: References: Comments: In-reply-to "Ilia Kantor" message dated "Mon, 29 Aug 2005 00:59:33 +0400" Date: Sun, 28 Aug 2005 17:30:48 -0400 Message-ID: <26927.1125264648@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006] X-Spam-Level: X-Archive-Number: 200508/491 X-Sequence-Number: 14238 "Ilia Kantor" writes: > So I just suggest that the planner should take this into account and > "propagate" the condition outside or inside for planning if needed. I believe it does this already for equality conditions, but not for inequalities. regards, tom lane