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

2013-05-22 Thread 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-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] 
> på vegne av Antonio Goméz Soto [antonio.gomez.s...@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

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

2013-05-22 Thread 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-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] 
på vegne av Antonio Goméz Soto [antonio.gomez.s...@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

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

2013-05-22 Thread 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


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

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

2013-05-22 Thread 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.




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