Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

    12.0p Generated SQL for PostgreSQL case insensitive LIKE comparisons / Add ILIKE support

    Hi Isomorphic,

    please see this PostgreSQL 11.4 create table code, and the SQL you typically generate for iContains.
    We have a big city data table here (from Geonames) and ask it with a few ... iContains ... OR ... iContains ... in order to make suggestions in a ComboBoxItem.

    It turns out that the SQL you generate (LOWER() around the column name) makes Postgres 11.4 not use the index, while it would do so for ILIKE.
    Please see this code and the resulting plans:

    Test table:
    Code:
    --Needed for indexes supporting wildcard searches
    CREATE EXTENSION pg_trgm;
    CREATE EXTENSION btree_gin;
    
    drop table test;
    create table test
    (
        id          bigint not null
            constraint test_pkey primary key
            constraint nnc_test_id check (id IS NOT NULL),
        id_in_order bigint not null
            constraint nnc_test_id_in_order check (id_in_order IS NOT NULL),
        text_lc      varchar(40)
            constraint nnc_test_text_lc check (text_lc IS NOT NULL),
        text_uc     varchar(40)
            constraint nnc_test_text_uc check (text_uc IS NOT NULL)
    );
    
    INSERT INTO test(id, id_in_order, text_lc, text_uc)
    SELECT series.i, --id
           series.i / 10000, --100 distinct values
           LOWER(md5(random()::text)),
           UPPER(md5(random()::text))
    FROM generate_series(1, 1000000) as series(i);
    
    ANALYZE VERBOSE test;
    
    create index test_gin on test USING gin (id_in_order, text_lc gin_trgm_ops, text_uc gin_trgm_ops);
    create index test_normal on test (id_in_order, text_lc, text_uc);
    
    --See generated data
    select * from test where id_in_order = 50 order by id_in_order, text_lc, text_uc;
    
    --ILIKE uses index (same result as below)
    select id_in_order, text_lc, text_uc, id
    from test
    where id_in_order = 50
      and ((text_lc ilike '%abcd%' and text_lc is not null) or
           (text_uc ilike '%abcd%' and text_uc is not null))
    order by id_in_order, text_lc, text_uc;
    
    --LOWER around column does not use index (same result as above)
    select id_in_order, text_lc, text_uc, id
    from test
    where id_in_order = 50
      and ((lower(text_lc) like lower('%abcd%') and text_lc is not null) or
           (upper(text_uc) like upper('%abcd%') and text_uc is not null))
    order by id_in_order, text_lc, text_uc;
    Plan 1 (good) (can paste it here):
    Code:
    QUERY PLAN
    "Sort  (cost=654.78..654.78 rows=2 width=82)"
    "  Sort Key: text_lc, text_uc"
    "  ->  Bitmap Heap Scan on test  (cost=646.80..654.77 rows=2 width=82)"
    "        Recheck Cond: ((((text_lc)::text ~~* '%abcd%'::text) OR ((text_uc)::text ~~* '%abcd%'::text)) AND (id_in_order = 50))"
    "        ->  BitmapAnd  (cost=646.80..646.80 rows=2 width=0)"
    "              ->  BitmapOr  (cost=73.50..73.50 rows=200 width=0)"
    "                    ->  Bitmap Index Scan on test_gin  (cost=0.00..36.75 rows=100 width=0)"
    "                          Index Cond: ((text_lc)::text ~~* '%abcd%'::text)"
    "                    ->  Bitmap Index Scan on test_gin  (cost=0.00..36.75 rows=100 width=0)"
    "                          Index Cond: ((text_uc)::text ~~* '%abcd%'::text)"
    "              ->  Bitmap Index Scan on test_normal  (cost=0.00..573.05 rows=9667 width=0)"
    "                    Index Cond: (id_in_order = 50)"
    Plan 2 (bad):
    Code:
    QUERY PLAN
    "Index Scan using test_normal on test  (cost=0.55..13488.27 rows=154 width=82)"
    "  Index Cond: (id_in_order = 50)"
    "  Filter: (((lower((text_lc)::text) ~~ '%abcd%'::text) AND (text_lc IS NOT NULL)) OR ((upper((text_uc)::text) ~~ '%ABCD%'::text) AND (text_uc IS NOT NULL)))"
    Could you change the generated SQL here in order to use ILIKE?

    Thank you & Best regards
    Blama

    #2
    We agree that this would be a worthwhile enhancement to our Postgres support. A developer is scheduled to make this change, and we will post back on this thread soon.

    Comment

    Working...
    X