|
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 |
