From: Václav Ovsík <vaclav.ovsik@i.cz>
To: pgsql-performance@postgresql.org
Subject: Re: poor execution plan because column dependence
Date: Fri, 15 Apr 2011 09:59:26 +0200
Message-ID: <20110415075926.GA26261@bobek.localdomain> (raw)
In-Reply-To: <13172.1302790244@sss.pgh.pa.us>
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>
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
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: vaclav.ovsik@i.cz
Subject: Re: poor execution plan because column dependence
In-Reply-To: <20110415075926.GA26261@bobek.localdomain>
* 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