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.69rows=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