pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery with "ILIKE ALL" does not use the index
5+ messages / 3 participants
[nested] [flat]
* Query with "ILIKE ALL" does not use the index
@ 2018-07-26 15:53 Nicolas Even <neven@ztel.org>
0 siblings, 1 reply; 5+ messages in thread
From: Nicolas Even @ 2018-07-26 15:53 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hi,
I have the following table:
Table "public.totoz"
Column | Type | Collation | Nullable | Default
-----------+--------------------------+-----------+----------+---------
name | character varying(512) | | not null |
Indexes:
"totoz_pkey" PRIMARY KEY, btree (name)
"totoz_name_trgrm_idx" gin (name gin_trgm_ops)
When I run the following query, it uses the totoz_name_trgrm_idx as expected:
explain analyze select name from totoz where name ilike '%tot%';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on totoz (cost=48.02..59.69 rows=3 width=11)
(actual time=0.205..0.446 rows=88 loops=1)
Recheck Cond: ((name)::text ~~* '%tot%'::text)
Heap Blocks: exact=85
-> Bitmap Index Scan on totoz_name_trgrm_idx (cost=0.00..48.02
rows=3 width=0) (actual time=0.177..0.177 rows=88 loops=1)
Index Cond: ((name)::text ~~* '%tot%'::text)
Planning time: 0.302 ms
Execution time: 0.486 ms
(7 rows)
However when I run the same (as far as I understand it) query but with
the ALL operator, the index is not used:
explain analyze select name from totoz where name ilike all(array['%tot%']);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Only Scan using totoz_pkey on totoz (cost=0.29..1843.64 rows=3
width=11) (actual time=3.854..20.757 rows=88 loops=1)
Filter: ((name)::text ~~* ALL ('{%tot%}'::text[]))
Rows Removed by Filter: 30525
Heap Fetches: 132
Planning time: 0.230 ms
Execution time: 20.778 ms
(6 rows)
I'd have expected the second query to use the totoz_name_trgrm_idx but
it doesn't. Why is that?
Thanks for your help!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query with "ILIKE ALL" does not use the index
@ 2018-07-26 16:44 Tom Lane <tgl@sss.pgh.pa.us>
parent: Nicolas Even <neven@ztel.org>
0 siblings, 2 replies; 5+ messages in thread
From: Tom Lane @ 2018-07-26 16:44 UTC (permalink / raw)
To: Nicolas Even <neven@ztel.org>; +Cc: pgsql-performance@lists.postgresql.org
Nicolas Even <neven@ztel.org> writes:
> However when I run the same (as far as I understand it) query but with
> the ALL operator, the index is not used:
> explain analyze select name from totoz where name ilike all(array['%tot%']);
There's only index support for "op ANY (array)", not "op ALL (array)".
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query with "ILIKE ALL" does not use the index
@ 2018-07-26 17:22 Matthew Hall <mhall@mhcomputing.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 5+ messages in thread
From: Matthew Hall @ 2018-07-26 17:22 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
On Jul 26, 2018, at 9:44 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Nicolas Even <neven@ztel.org> writes:
>> However when I run the same (as far as I understand it) query but with
>> the ALL operator, the index is not used:
>> explain analyze select name from totoz where name ilike all(array['%tot%']);
>
> There's only index support for "op ANY (array)", not "op ALL (array)".
>
> regards, tom lane
Nicolas,
Could you work around the limitation with a two-clause WHERE?
First clause ANY, second clause ALL.
I've done some similar tricks on similar sorts of queries.
Matthew.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query with "ILIKE ALL" does not use the index
@ 2018-07-26 19:17 Nicolas Even <neven@ztel.org>
parent: Matthew Hall <mhall@mhcomputing.net>
0 siblings, 0 replies; 5+ messages in thread
From: Nicolas Even @ 2018-07-26 19:17 UTC (permalink / raw)
To: Matthew Hall <mhall@mhcomputing.net>; +Cc: pgsql-performance@lists.postgresql.org
Hi Matthew,
I finally used "WHERE name ILIKE arr[1] AND name ILIKE ALL(arr)" which
works well enough for my use case.
Thank you
Nicolas
On 26 July 2018 at 19:22, Matthew Hall <mhall@mhcomputing.net> wrote:
> On Jul 26, 2018, at 9:44 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>> Nicolas Even <neven@ztel.org> writes:
>>> However when I run the same (as far as I understand it) query but with
>>> the ALL operator, the index is not used:
>>> explain analyze select name from totoz where name ilike all(array['%tot%']);
>>
>> There's only index support for "op ANY (array)", not "op ALL (array)".
>>
>> regards, tom lane
>
> Nicolas,
>
> Could you work around the limitation with a two-clause WHERE?
>
> First clause ANY, second clause ALL.
>
> I've done some similar tricks on similar sorts of queries.
>
> Matthew.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query with "ILIKE ALL" does not use the index
@ 2018-07-26 19:32 Nicolas Even <neven@ztel.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Nicolas Even @ 2018-07-26 19:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance@lists.postgresql.org
Thank you Tom
On 26 July 2018 at 18:44, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Nicolas Even <neven@ztel.org> writes:
>> However when I run the same (as far as I understand it) query but with
>> the ALL operator, the index is not used:
>> explain analyze select name from totoz where name ilike all(array['%tot%']);
>
> There's only index support for "op ANY (array)", not "op ALL (array)".
>
> regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2018-07-26 19:32 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-07-26 15:53 Query with "ILIKE ALL" does not use the index Nicolas Even <neven@ztel.org>
2018-07-26 16:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-07-26 17:22 ` Matthew Hall <mhall@mhcomputing.net>
2018-07-26 19:17 ` Nicolas Even <neven@ztel.org>
2018-07-26 19:32 ` Nicolas Even <neven@ztel.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox