From: Mezei Zoltán <mezei.zoltan@telefor.hu>
To: pgsql-performance@postgresql.org
Subject: Deceiding which index to use
Date: Fri, 09 Mar 2007 14:20:15 +0100
Message-ID: <45F15F0F.8080403@telefor.hu> (raw)
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
<font size="-1"><font face="Tahoma">Hi!<br>
<br>
I have two tables with some indices on them:<br>
<br>
CREATE TABLE subscriber<br>
(<br>
id serial NOT NULL,<br>
anumber character varying(32) NOT NULL,<br>
CONSTRAINT subscriber_pk PRIMARY KEY (id)<br>
) <br>
<br>
CREATE INDEX anumber_idx_numeric<br>
ON subscriber<br>
USING btree<br>
(anumber::numeric);<br>
<br>
CREATE TABLE output_message_log<br>
(<br>
id serial NOT NULL,<br>
subscriber_id integer NOT NULL,<br>
crd timestamp without time zone NOT NULL DEFAULT now(),<br>
CONSTRAINT output_message_log_pk PRIMARY KEY (id),<br>
CONSTRAINT subscriber_fk FOREIGN KEY (subscriber_id)<br>
REFERENCES subscriber (id) MATCH SIMPLE<br>
ON UPDATE NO ACTION ON DELETE NO ACTION,<br>
) <br>
<br>
CREATE INDEX crd_idx<br>
ON output_message_log<br>
USING btree<br>
(crd);<br>
<br>
CREATE INDEX subscriber_id_idx<br>
ON output_message_log<br>
USING btree<br>
(subscriber_id);<br>
<br>
I would like to run a query like this one:<br>
<br>
select l.id<br>
from output_message_log l join subscriber s on l.subscriber_id = s.id <br>
where s.anumber::numeric = 5555555555<br>
order by l.crd desc<br>
limit 41<br>
offset 20<br>
<br>
The thing I do not understand is why postgresql wants to use crd_idx:<br>
<br>
"Limit (cost=4848.58..14788.18 rows=41 width=12) (actual
time=7277.115..8583.814 rows=41 loops=1)"<br>
" -> Nested Loop (cost=0.00..1195418.42 rows=4931 width=12)
(actual time=92.083..8583.713 rows=61 loops=1)"<br>
" -> Index Scan Backward using crd_idx on output_message_log
l (cost=0.00..17463.80 rows=388646 width=16) (actual
time=0.029..975.095 rows=271447 loops=1)"<br>
" -> Index Scan using subscriber_pk on subscriber s
(cost=0.00..3.02 rows=1 width=4) (actual time=0.026..0.026 rows=0
loops=271447)"<br>
" Index Cond: ("outer".subscriber_id = s.id)"<br>
" Filter: ((anumber)::numeric = 36308504669::numeric)"<br>
"Total runtime: 8584.016 ms"<br>
<br>
I would like postgresql to use </font></font><font size="-1"><font
face="Tahoma">subscriber_id_idx which resulst in a far less execution
time on this database.<br>
<br>
I tried to lower random_page_cost, but that didn't help as an index is
already used, just not the "good" one.<br>
<br>
Could you please comment on this issue and suggest some possible
soulutions?<br>
<br>
Thanks,<br>
<br>
Zizi<br>
</font></font><font size="-1"><font face="Tahoma"><br>
</font></font>
</body>
</html>
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: mezei.zoltan@telefor.hu
Subject: Re: Deceiding which index to use
In-Reply-To: <45F15F0F.8080403@telefor.hu>
* 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