`ILIKE` expression support for AlloyDB

Google Cloud Database
1

In the Query types that benefit from the columnar engine section of the AlloyDB documentation, only LIKE, SUBSTR and TRIM are listed as supported expressions in the columnar engine. However, a quick query plan shows that ILIKE also uses a columnar scan.

db=> explain select * from <table_name> where <text_column> ilike '%<keyword>%';
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Append  (cost=20.00..54.80 rows=1131 width=133)
   ->  Custom Scan (columnar scan) on <table_name>  (cost=20.00..50.79 rows=1130 width=133)
         Filter: (<text_column> ~~* '%<keyword>%'::text)
         Columnar cache search mode: native
   ->  Seq Scan on <table_name>  (cost=0.00..4.01 rows=1 width=133)
         Filter: (<text_column> ~~* '%<keyword>%'::text)
(6 rows)

It would be nice if the documentation could be updated to reflect this.

1 Like