DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.au;
	h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=TorDjPV550t9Bz178NWUSx5GTcowkQegQY1JsSdckP6iqBD7J49R9DzhfEUb363/7VvzaciQ+DCOb1UlbUksQC7FBCMmYClkoFbXaVK3qn5vOSO3iF91RYBnQLnn7sMUhiLtLBljKi/vL3w6YFdTTzkjZu0LNxVLVOTQCg9lte8=
	;
Message-ID: <20060406235622.27528.qmail@web52307.mail.yahoo.com>
Date: Fri, 7 Apr 2006 09:56:21 +1000 (EST)
From: Brian Herlihy <btherl@yahoo.com.au>
Subject: Re: Query planner is using wrong index.
To: pgsql-performance@postgresql.org
In-Reply-To: <1144340430.32289.97.camel@localhost.localdomain>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit


--- Ragnar <gnari@hive.is> wrote:

> On fös, 2006-04-07 at 00:01 +1000, Brian Herlihy wrote:
> >  Index Scan using p2_p3_idx on t  (cost=0.00..6.02 rows=1 width=102)
> (actual
> > time=2793.247..2793.247 rows=0 loops=1)
> >    Index Cond: (((p2)::text = 'uk.altavista.com'::text) AND ((p3)::text =
> > 'web/results?itag=&q=&kgs=&kls='::text))
> >    Filter: ((p1)::text = 'a'::text)
> >  Total runtime: 2793.303 ms
> > (4 rows)
> 
> try to add an ORDER BY clause:
> 
> explain analyze 
>   select * from t 
>   WHERE p1 = 'a'
>     and p2 = 'uk.altavista.com'
>     AND p3 = 'web/results?itag=&q=&kgs=&kls='
>   ORDER BY p1,p2,p3;
> 
> this might push the planner into using the primary key
> 
> gnari
> 

Thankyou very much, that works very well for select.  However, I need it to
work for update as well.  Is there an equivalent way to force use of an index
for updates?

Here are the results for select:

db# explain analyze select * from t WHERE p1 = 'a' and p2 = 'uk.altavista.com'
AND p3 = 'web/results?itag=&q=&kgs=&kls=' order by p1,p2,p3;
                                                                             
QUERY PLAN                                                                     
  
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using t_pkey on t  (cost=0.00..6.02 rows=1 width=102) (actual
time=32.519..32.519 rows=0 loops=1)
   Index Cond: (((p1)::text = 'a'::text) AND ((p2)::text =
'uk.altavista.com'::text) AND ((p3)::text =
'web/results?itag=&q=&kgs=&kls='::text))
 Total runtime: 32.569 ms
(3 rows)

db# explain analyze select * from t WHERE p1 = 'a' and p2 = 'uk.altavista.com'
AND p3 = 'web/results?itag=&q=&kgs=&kls=';
                                                              QUERY PLAN       

---------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using p2_p3_idx on t  (cost=0.00..6.02 rows=1 width=102) (actual
time=2790.364..2790.364 rows=0 loops=1)
   Index Cond: (((p2)::text = 'uk.altavista.com'::text) AND ((p3)::text =
'web/results?itag=&q=&kgs=&kls='::text))
   Filter: ((p1)::text = 'a'::text)
 Total runtime: 2790.420 ms
(4 rows)


But I cannot add an "order by" to an update.

The other idea I came up with last night was to change p2_p3_idx so it indexes
a value derived from p2 and p3, rather than p2 and p3 themselves.  This would
"hide" this index from the optimizer, forcing it to use the primary key.

I am really surprised that I have to go through such contortions just to use
the primary key!  This area of Postgres needs improvement.

Thanks,
Brian