From: Brian Herlihy <btherl@yahoo.com.au>
To: pgsql-performance@postgresql.org
Subject: Re: Query planner is using wrong index.
Date: Fri, 7 Apr 2006 09:56:21 +1000 (EST)
Message-ID: <20060406235622.27528.qmail@web52307.mail.yahoo.com> (raw)
In-Reply-To: <1144340430.32289.97.camel@localhost.localdomain>
--- 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
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: btherl@yahoo.com.au
Subject: Re: Query planner is using wrong index.
In-Reply-To: <20060406235622.27528.qmail@web52307.mail.yahoo.com>
* 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