Re: Slow query and using wrong index, how to fix? Probably naive question.. - Mailing list pgsql-general

From Albe Laurenz
Subject Re: Slow query and using wrong index, how to fix? Probably naive question..
Date
Msg-id A737B7A37273E048B164557ADEF4A58B058209D2@ntex2010a.host.magwien.gv.at
Whole thread Raw
In response to Slow query and using wrong index, how to fix? Probably naive question..  (Antonio Goméz Soto<antonio.gomez.soto@gmail.com>)
List pgsql-general
Antonio Goméz Soto wrote:
> 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?

You'd need the output of EXPLAIN ANALYZE to know why.

I assume that the index is chosen because of the ORDER BY clause, otherwise
it would probably have been a full table scan.

The index you mention can be used efficiently for
"where (lookup = 'phone' and lookupid = '672')", but the OR condition
makes it less useful.

Yours,
Laurenz Albe


pgsql-general by date:

Previous
From: Antonio Goméz Soto
Date:
Subject: Slow query and using wrong index, how to fix? Probably naive question..
Next
From: Leif Gunnar Erlandsen
Date:
Subject: Re: Slow query and using wrong index, how to fix? Probably naive question..