From: Tom Lane <tgl@sss.pgh.pa.us>
To: Václav Ovsík <vaclav.ovsik@i.cz>
Cc: pgsql-performance@postgresql.org
Subject: Re: poor execution plan because column dependence
Date: Tue, 12 Apr 2011 20:52:15 -0400
Message-ID: <20568.1302655935@sss.pgh.pa.us> (raw)
In-Reply-To: <20110412232343.GA15966@bobek.localdomain>
References: <20110412232343.GA15966@bobek.localdomain>
=?iso-8859-1?Q?V=E1clav_Ovs=EDk?= <vaclav.ovsik@i.cz> 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
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, vaclav.ovsik@i.cz
Subject: Re: poor execution plan because column dependence
In-Reply-To: <20568.1302655935@sss.pgh.pa.us>
* 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