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

Поиск
Список
Период
Сортировка
От Antonio Goméz Soto
Тема Very simple select, using index for ordering, but not for selecting. How to make it faster?
Дата
Msg-id 519D1EB0.9020301@gmail.com
обсуждение исходный текст
Ответы Re: Very simple select, using index for ordering, but not for selecting. How to make it faster?  (Shaun Thomas <sthomas@optionshouse.com>)
Список 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



В списке pgsql-general по дате отправления:

Предыдущее
От: Ray Stell
Дата:
Сообщение: Re: pg_upgrade -u
Следующее
От: Shaun Thomas
Дата:
Сообщение: Re: Very simple select, using index for ordering, but not for selecting. How to make it faster?