Re: Query planner is using wrong index.

--- 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