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

Leif,

of course. This performs much better (far below one second).

Thanks!
Antonio

Op 22-05-13 11:28, Leif Gunnar Erlandsen schreef:
> You might want to try with UNION and then sort the result of this query.
>
> The index history_lookup_lookupid_creator_index wont be used when you are having an "OR" in your WHERE statement.
>
> select history.id, history.created, creator, contact, history.type, lookup, lookupid, value
> from history
> where (lookup = 'phone' and lookupid = '672')
> union
> select history.id, history.created, creator, contact, history.type, lookup, lookupid, value
> from history
> where creator = '790'
>
> Leif Gunnar Erlandsen
>
>
>
> ________________________________________
> Fra: pgsql-general-owner(at)postgresql(dot)org [pgsql-general-owner(at)postgresql(dot)org] på vegne av Antonio Goméz Soto [antonio(dot)gomez(dot)soto(at)gmail(dot)com]
> Sendt: 22. mai 2013 10:50
> Til: pgsql-general(at)postgresql(dot)org
> Emne: [GENERAL] Slow query and using wrong index, how to fix? Probably naive question..
>
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>