Обсуждение: Slow query and using wrong index, how to fix? Probably naive question..

Поиск
Список
Период
Сортировка

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

От
Antonio Goméz Soto
Дата:
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.

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

От
Albe Laurenz
Дата:
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

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

От
Leif Gunnar Erlandsen
Дата:
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

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

От
Antonio Goméz Soto
Дата:
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
>

Чтобы сделать работу с сайтом удобнее, мы используем cookie и аналитический сервис «Яндекс.Метрика». Продолжая пользоваться сайтом, вы соглашаетесь с их использованием.