Deceiding which index to use - Mailing list pgsql-performance

From Mezei Zoltán
Subject Deceiding which index to use
Date
Msg-id 45F15F0F.8080403@telefor.hu
Whole thread Raw
Responses Re: Deceiding which index to use  (Richard Huxton <dev@archonet.com>)
List pgsql-performance
<font size="-1"><font face="Tahoma">Hi!<br /><br /> I have two tables with some indices on them:<br /><br /> CREATE
TABLEsubscriber<br /> (<br />   id serial NOT NULL,<br />   anumber character varying(32) NOT NULL,<br />   CONSTRAINT
subscriber_pkPRIMARY 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_pkPRIMARY KEY (id),<br />   CONSTRAINT subscriber_fk FOREIGN KEY (subscriber_id)<br />      
REFERENCESsubscriber (id) MATCH SIMPLE<br />       ON UPDATE NO ACTION ON DELETE NO ACTION,<br /> ) <br /><br /> CREATE
INDEXcrd_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
runa 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 />
Thething I do not understand is why postgresql wants to use crd_idx:<br /><br /> "Limit  (cost=4848.58..14788.18
rows=41width=12) (actual time=7277.115..8583.814 rows=41 loops=1)"<br /> "  ->  Nested Loop  (cost=0.00..1195418.42
rows=4931width=12) (actual time=92.083..8583.713 rows=61 loops=1)"<br /> "        ->  Index Scan Backward using
crd_idxon 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.026rows=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
wouldlike postgresql to use </font></font><font size="-1"><font face="Tahoma">subscriber_id_idx which resulst in a far
lessexecution time on this database.<br /><br /> I tried to lower random_page_cost, but that didn't help as an index is
alreadyused, 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>

pgsql-performance by date:

Previous
From: Richard Huxton
Date:
Subject: Re: configuring new server / many slow disks?
Next
From: Zoolin Lin
Date:
Subject: Re: Any advantage to integer vs stored date w. timestamp