Re: poor execution plan because column dependence

Dear Tom,

On Thu, Apr 14, 2011 at 10:10:44AM -0400, Tom Lane wrote:
> =?iso-8859-1?Q?V=E1clav_Ovs=EDk?= <vaclav(dot)ovsik(at)i(dot)cz> writes:
> > I'm not certain about your sentence touching int4eq() and index. The
> > execution plan as show in my previous mail contains information about
> > using index tickets5:
>
> > -> Index Scan using tickets5 on tickets main (cost=0.00..4.38 rows=1 width=162) (actual time=0.006..0.006 rows=0 loops=15593)
> > Index Cond: (main.id = transactions_1.objectid)
> > Filter: (((main.status)::text <> 'deleted'::text) AND (main.lastupdated > '2008-12-31 23:00:00'::timestamp without time zone) AND (main.created > '2005-12-31 23:00:00'::timestamp without time zone) AND int4eq(main.effectiveid, main.id) AND (main.queue = 15) AND ((main.type)::text = 'ticket'::text) AND ((main.status)::text = 'resolved'::text))
>
> > That means tickets5 index was used for int4eq(main.effectiveid, main.id).
> > Is it right? Or am I something missing?
>
> No, the clause that's being used with the index is
> main.id = transactions_1.objectid
> The "filter condition" is just along for the ride --- it doesn't matter
> what sort of expressions are in there, so long as they only use
> variables available at this point in the plan. But if you had coded
> that clause as
> int4eq(main.id, transactions_1.objectid)
> it would have been unable to create this plan at all.

Thanks you for the explanation and the patience with me. I have red the
chapter "Multicolumn Indexes" in the Pg doc and discover new things for
me. The planner can use multicolumn index with an index leftmost field
alone - I missed this. I understand things a bit better now.
Thanks!
Best Regards
--
Zito