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.niamode...@%' 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.niamode...@%'
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.niamode...@%' 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.niamode...@%'::text)
(3 rows)
Plan without "LIMIT":
explain select email FROM book_users WHERE lower(reverse_str(email))
LIKE 'moc.niamode...@%' 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.niamode...@%'::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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
