James Inform wrote:
> -- This is not giving a match
> select * from icutest where data ilike 'mytest';
>
> -- BUT THIS GIVES A MATCH:
>
> select * from icutest where data2 ilike 'mytest';
>
> -- So it seems to be especially related to the scenario where a primary key
> / index exists.
>
Yes. It is because of the index that the code checks if the ILIKE
can be evaluated with an index lookup. Otherwise it doesn't.
If you feel like recompiling with a temporary fix against v11.4 to do
your own tests, please try the attached patch.
Here's the result for me:
* Unpatched version:
postgres=# explain analyze select * from icutest where data ilike 'mytest';
QUERY PLAN
-------------------------------------------------------------------------------------------------------
---------------------
Index Only Scan using icutest_pkey on icutest (cost=0.15..8.17 rows=1
width=32) (actual time=0.013..0
.013 rows=0 loops=1)
Index Cond: (data = 'mytest'::text)
Filter: (data ~~* 'mytest'::text)
Heap Fetches: 0
Planning Time: 0.593 ms
Execution Time: 0.035 ms
* Patched version:
postgres=# explain analyze select * from icutest where data ilike 'mytest';
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on icutest (cost=0.00..27.00 rows=1 width=32) (actual
time=0.122..0.123 rows=1 loops=1)
Filter: (data ~~* 'mytest'::text)
Planning Time: 0.081 ms
Execution Time: 0.144 ms
Notice how the patched version ignores the index and correctly finds the
row versus the unpatched version.
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite