To: =?iso-8859-1?Q?V=E1clav_Ovs=EDk?= <vaclav.ovsik@i.cz>
cc: pgsql-performance@postgresql.org
Subject: Re: poor execution plan because column dependence 
In-reply-to: <20110414081152.GB11328@bobek.localdomain> 
References: <20110412232343.GA15966@bobek.localdomain>
	<20568.1302655935@sss.pgh.pa.us>
	<20110413082139.GB24050@bobek.localdomain>
	<6361.1302711846@sss.pgh.pa.us>
	<20110414081152.GB11328@bobek.localdomain>
Comments: In-reply-to =?iso-8859-1?Q?V=E1clav_Ovs=EDk?= <vaclav.ovsik@i.cz>
	message dated "Thu, 14 Apr 2011 10:11:52 +0200"
Date: Thu, 14 Apr 2011 10:10:44 -0400
Message-ID: <13172.1302790244@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

=?iso-8859-1?Q?V=E1clav_Ovs=EDk?= <vaclav.ovsik@i.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.

			regards, tom lane