Search Postgresql Archives

Slow query and using wrong index, how to fix? Probably naive question..

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,

I am using postgresql 8.1 (CentOS5). I have the following table:

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 history.id, history.created, creator, contact, history.type, lookup, lookupid, value from history  where (lookup = 'phone' and lookupid = '672') or creator = '790' order by history.creator desc limit 1000;
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..132041.59 rows=1000 width=58)
   ->  Index Scan Backward using history_creator_index on history  (cost=0.00..11746815.97 rows=88963 width=58)
         Filter: (((lookup = 'phone'::text) AND (lookupid = 672)) OR (creator = 790))
(3 rows)

This table contains 2 million rows, the query takes 800 seconds on SSD HD.

I think - probably naive - the query should use the history_lookup_lookupid_creator_index.

Why doesn't it, and how can I speed up the query?

Thanks,
Antonio.




-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux