Date: Fri, 15 Apr 2011 09:59:26 +0200
From: =?iso-8859-1?Q?V=E1clav_Ovs=EDk?= <vaclav.ovsik@i.cz>
To: pgsql-performance@postgresql.org
Subject: Re: poor execution plan because column dependence
Message-ID: <20110415075926.GA26261@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>
	<13172.1302790244@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <13172.1302790244@sss.pgh.pa.us>
User-Agent: Mutt/1.5.21 (2010-09-15)

Dear Tom,

On Thu, Apr 14, 2011 at 10:10:44AM -0400, Tom Lane wrote:
> =?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.

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