Re: slow query

"Scott Marlowe" <scott.marlowe@gmail.com> writes:

>          ->  Index Scan using users_orgid_idx on users u  (cost=0.00..129.52 rows=5 width=271) (actual
time=843.825..860.638rows=0 loops=35) 
>                Index Cond: (u.orgid = j2.orgid)
>                Filter: ((u.type_id < 10) AND (u.deleted = 0) AND ((lower((u.lname)::text) ~~ 'boat%'::text) OR
(lower((u.fname)::text)~~ 'boat%'::text) OR (lower((u.username)::text) ~~ 'boat%'::text) OR (lower(u.option1) ~~
'boat%'::text)OR (lower((u.email)::text) ~~ '%boat%'::text) OR (lower(u.external_id) = 'boat'::text))) 

Not sure if this is what's going on but I find the high startup time for this
index scan suspicious. Either there are a lot of dead tuples (which would
explain the second run being fast if it marks them all as lp_dead) or there
are a lot of matching index pointers which fail those other constraints.
Assuming it's the latter perhaps some other index definition would let it zero
in on the right tuples more quickly instead of having to grovel through a lot
of irrelevant rows?

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!