Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id C74F91337BCF for ; Tue, 12 Apr 2011 21:52:26 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 14555-06 for ; Wed, 13 Apr 2011 00:52:21 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 2CA171337BC4 for ; Tue, 12 Apr 2011 21:52:19 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p3D0qFO5020569; Tue, 12 Apr 2011 20:52:16 -0400 (EDT) To: =?iso-8859-1?Q?V=E1clav_Ovs=EDk?= cc: pgsql-performance@postgresql.org Subject: Re: poor execution plan because column dependence In-reply-to: <20110412232343.GA15966@bobek.localdomain> References: <20110412232343.GA15966@bobek.localdomain> Comments: In-reply-to =?iso-8859-1?Q?V=E1clav_Ovs=EDk?= message dated "Wed, 13 Apr 2011 01:23:43 +0200" Date: Tue, 12 Apr 2011 20:52:15 -0400 Message-ID: <20568.1302655935@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201104/180 X-Sequence-Number: 43242 =?iso-8859-1?Q?V=E1clav_Ovs=EDk?= writes: > I think the execution plan is poor. Better would be to filter table attachments > at first and then join the rest. The reason is a bad estimate on number of rows > returned from table tickets (85 estimated -> 25410 in the reality). > ... > The problem is the strong dependance between id and effectiveid. The RT > documentation says: > EffectiveId: > By default, a ticket's EffectiveId is the same as its ID. RT supports the > ability to merge tickets together. When you merge a ticket into > another one, RT sets the first ticket's EffectiveId to the second > ticket's ID. RT uses this data to quickly look up which ticket > you're really talking about when you reference a merged ticket. > I googled the page http://wiki.postgresql.org/wiki/Cross_Columns_Stats > Maybe I identified the already documented problem. What I can do with this > situation? Some workaround? Yeah, that main.EffectiveId = main.id clause is going to be underestimated by a factor of about 200, which is most though not all of your rowcount error for that table. Not sure whether you can do much about it, if the query is coming from a query generator that you can't change. If you can change it, try replacing main.EffectiveId = main.id with the underlying function, eg if they're integers use int4eq(main.EffectiveId, main.id). This will bypass the overoptimistic estimator for the "=" operator and get you a default selectivity estimate of (IIRC) 0.3333. Which is still off, but only by 3x not 200x, and that should be close enough to get a decent plan. regards, tom lane