Very simple select, using index for ordering, but not for selecting. How to make it faster?

From: Antonio Goméz Soto <antonio(dot)gomez(dot)soto(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Very simple select, using index for ordering, but not for selecting. How to make it faster?
Date: 2013-05-22 19:38:24
Message-ID: 519D1EB0.9020301@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I have the following simple query on a simple table:

system=# select * from history where lookup = 'trunk' and lookupid = '248' order by created desc limit 1000;

system=# \d history
Table "public.history"
Column | Type | Modifiers
----------+--------------------------+------------------------------------------------------
id | integer | not null default nextval('history_id_seq'::regclass)
created | timestamp with time zone |
creator | integer | not null default 1
contact | integer | not null default 1
type | character varying | not null default ''::character varying
lookup | text |
lookupid | integer | not null default 1
value | text |
Indexes:
"history_pkey" PRIMARY KEY, btree (id)
"history_created_index" btree (created)
"history_creator_index" btree (creator)
"history_lookup_lookupid_creator_index" btree (lookup, lookupid, creator)
"history_lookup_lookupid_index" btree (lookup, lookupid)
Foreign-key constraints:
"history_contact_constraint" FOREIGN KEY (contact) REFERENCES contact(id)
"history_creator_constraint" FOREIGN KEY (creator) REFERENCES contact(id)

system=# explain select * from history where lookup = 'trunk' and lookupid = '248' order by created desc limit 1000; QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..14799.28 rows=1000 width=58)
-> Index Scan Backward using history_created_index on history (cost=0.00..12201987.90 rows=824499 width=58)
Filter: ((lookup = 'trunk'::text) AND (lookupid = 248))
(3 rows)

Why doesn't it use the index specified in select? How can I change the query to make it faster?

Thanks,
Antonio

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Shaun Thomas 2013-05-22 19:49:49 Re: Very simple select, using index for ordering, but not for selecting. How to make it faster?
Previous Message Ray Stell 2013-05-22 19:05:57 Re: pg_upgrade -u