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

Started by Antonio Goméz Sotoalmost 13 years ago4 messagesgeneral

antonio.gomez.soto@gmail.com

almost 13 years ago

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Laurenz Albe

laurenz.albe@cybertec.at

almost 13 years ago

In reply to: Antonio Goméz Soto (#1)

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

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

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

Leif Gunnar Erlandsen

leif@basefarm.no

almost 13 years ago

In reply to: Antonio Goméz Soto (#1)

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

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@postgresql.org [pgsql-general-owner@postgresql.org] på vegne av Antonio Goméz Soto [antonio.gomez.soto@gmail.com]
Sendt: 22. mai 2013 10:50
Til: pgsql-general@postgresql.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:

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Antonio Goméz Soto

antonio.gomez.soto@gmail.com

almost 13 years ago

In reply to: Leif Gunnar Erlandsen (#3)

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@postgresql.org [pgsql-general-owner@postgresql.org] på vegne av Antonio Gomï¿½z Soto [antonio.gomez.soto@gmail.com]
Sendt: 22. mai 2013 10:50
Til: pgsql-general@postgresql.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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