Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 7B6679FB577 for ; Fri, 9 Mar 2007 09:53:38 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 59136-08 for ; Fri, 9 Mar 2007 09:53:25 -0400 (AST) X-Greylist: delayed 00:33:08.50325 by SQLgrey-1.7.4 Received: from mail.telefor.hu (mail.telefor.hu [193.68.53.97]) by postgresql.org (Postfix) with ESMTP id 651B19FB595 for ; Fri, 9 Mar 2007 09:53:30 -0400 (AST) Received: from telefor-server.int.telefor-new.hu (telefor-server.int.telefor-new.hu [10.0.20.100]) by mail.telefor.hu (Postfix) with ESMTP id D1CAE1C055 for ; Fri, 9 Mar 2007 14:20:15 +0100 (CET) Received: from [10.0.20.33] ([10.0.20.33]) by telefor-server.int.telefor-new.hu with Microsoft SMTPSVC(6.0.3790.1830); Fri, 9 Mar 2007 14:20:15 +0100 Message-ID: <45F15F0F.8080403@telefor.hu> Date: Fri, 09 Mar 2007 14:20:15 +0100 From: =?UTF-8?B?TWV6ZWkgWm9sdMOhbg==?= Organization: Telefor kft. User-Agent: Thunderbird 2.0b2 (Windows/20070116) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Deceiding which index to use Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit X-OriginalArrivalTime: 09 Mar 2007 13:20:15.0659 (UTC) FILETIME=[ACED5FB0:01C7624D] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=BAYES_50, HTML_MESSAGE, MIME_HTML_ONLY X-Spam-Level: X-Archive-Number: 200703/191 X-Sequence-Number: 23596 Hi!

I have two tables with some indices on them:

CREATE TABLE subscriber
(
id serial NOT NULL,
anumber character varying(32) NOT NULL,
CONSTRAINT subscriber_pk PRIMARY KEY (id)
)

CREATE INDEX anumber_idx_numeric
ON subscriber
USING btree
(anumber::numeric);

CREATE TABLE output_message_log
(
id serial NOT NULL,
subscriber_id integer NOT NULL,
crd timestamp without time zone NOT NULL DEFAULT now(),
CONSTRAINT output_message_log_pk PRIMARY KEY (id),
CONSTRAINT subscriber_fk FOREIGN KEY (subscriber_id)
      REFERENCES subscriber (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
)

CREATE INDEX crd_idx
ON output_message_log
USING btree
(crd);

CREATE INDEX subscriber_id_idx
ON output_message_log
USING btree
(subscriber_id);

I would like to run a query like this one:

select l.id
from output_message_log l join subscriber s on l.subscriber_id = s.id
where s.anumber::numeric = 5555555555
order by l.crd desc
limit 41
offset 20

The thing I do not understand is why postgresql wants to use crd_idx:

"Limit (cost=4848.58..14788.18 rows=41 width=12) (actual time=7277.115..8583.814 rows=41 loops=1)"
" -> Nested Loop (cost=0.00..1195418.42 rows=4931 width=12) (actual time=92.083..8583.713 rows=61 loops=1)"
"        -> 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)"
"        -> 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)"
"              Index Cond: ("outer".subscriber_id = s.id)"
"              Filter: ((anumber)::numeric = 36308504669::numeric)"
"Total runtime: 8584.016 ms"

I would like postgresql to use subscriber_id_idx which resulst in a far less execution time on this database.

I tried to lower random_page_cost, but that didn't help as an index is already used, just not the "good" one.

Could you please comment on this issue and suggest some possible soulutions?

Thanks,

Zizi