Re: poor execution plan because column dependence

=?iso-8859-1?Q?V=E1clav_Ovs=EDk?= <vaclav(dot)ovsik(at)i(dot)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