Planner behaviour

[Sebastian Böhm]

Hi,

I have a table with emails. I want to search this column with wildcards.

To make a wildcard like this possible: "*@somedomain.com" , I use this  
query:

SELECT * FROM users WHERE lower(reverse_str(email))) LIKE  
'moc.niamodemos@%' ORDER BY email

(I use reverse_str as the index only gets queried when the constant  
part of the string matched by LIKE is at the beginning of the string)

to speed things up I have a index on "lower(reverse_str(email))"

Everything works, the index is queried

Now the strange part:

As soos as I add "LIMIT 10" to the query:

SELECT * FROM users WHERE reverse_str(email)) LIKE 'moc.niamodemos@%'  
ORDER BY email LIMIT 10

the database does not use the "reverse_str(email)" index, but just the  
"email" index, and the query takes endless.

Why?
What can I do?



Plan with "LIMIT" :

explain select email FROM book_users WHERE lower(reverse_str(email))  
LIKE 'moc.niamodemos@%' order by email limit 10;
                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------
  Limit  (cost=0.00..8094.69 rows=10 width=23)
    ->  Index Scan using book_users_email_key on book_users  
(cost=0.00..16868526.16 rows=20839 width=23)
          Filter: (lower(reverse_str((email)::text)) ~~  
'moc.niamodemos@%'::text)
(3 rows)



Plan without "LIMIT":

explain select email FROM book_users WHERE lower(reverse_str(email))  
LIKE 'moc.niamodemos@%' order by email;
                                                                           QUERY
  PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=70639.69..70691.79 rows=20839 width=23)
    Sort Key: email
    ->  Bitmap Heap Scan on book_users  (cost=635.19..69144.81  
rows=20839 width=23)
          Filter: (lower(reverse_str((email)::text)) ~~  
'moc.niamodemos@%'::text)
          ->  Bitmap Index Scan on book_users_lower_rev_email_key  
(cost=0.00..629.98 rows=20839 width=0)
                Index Cond: ((lower(reverse_str((email)::text)) >=  
'moc.niamodemos@'::text) AND (lower(reverse_str((email)::text)) <  
'moc.niamodemosA'::text))
(6 rows)


With LIMIT it takes endless, without only a fraction of a second.
PS: with LIMIT 100 the behavior switches to the same behavior as  
without limit

Thank you very much
Sebastian

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