--- Ragnar <gnari@xxxxxxx> 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
