gin not used on two columns at the same time in a JOIN

Question

I have two tables, products and products_names.

I am doing ILIKE matching in two columns with GIN index in both tables but GIN is only used if I do ILIKE on only one column.

I made a workaround by doing UNION but I would like to know why is it not working as I thought it should.

Both columns, n.name and e.producer are VARCHAR have GIN index on them:

CREATE INDEX products_producer_gin_idx ON products USING gin (producer gin_trgm_ops);
CREATE INDEX products_names_name_gin_idx ON products_names USING gin (name gin_trgm_ops);

SELECT with JOIN and ILIKE which does not use GIN:

testdb=# explain (analyze, verbose) 
            SELECT n.name, e.producer
            FROM products e
            INNER JOIN products_names n ON 
                n.product_id = e.product_id

            WHERE

                    n.name ilike '%eda%' or e.producer ilike '%eda%' 


limit 20;
                                                                                 QUERY PLAN                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..2725.92 rows=20 width=60) (actual time=0.582..62.658 rows=20 loops=1)
   Output: n.name, e.producer
   ->  Nested Loop  (cost=0.42..669928.73 rows=4916 width=60) (actual time=0.582..62.652 rows=20 loops=1)
         Output: n.name, e.producer
         ->  Seq Scan on public.products e  (cost=0.00..220800.16 rows=446716 width=29) (actual time=0.002..5.363 rows=17067 loops=1)
               Output: e.producer, e.product_id
         ->  Index Scan using products_names_pkey on public.products_names n  (cost=0.42..1.00 rows=1 width=39) (actual time=0.003..0.003 rows=0 loops=17067)
               Output: n.product_id, n.lang, n.name, n.name2, n.name3, n.products
               Index Cond: (n.product_id = e.product_id)
               Filter: (((n.name)::text ~~* '%eda%'::text) OR ((e.producer)::text ~~* '%eda%'::text))
               Rows Removed by Filter: 1
 Planning time: 0.559 ms
 Execution time: 62.677 ms
(13 Zeilen)

Zeit: 63,529 ms

SELECT on a single column n.name which uses GIN:

testdb=# explain (analyze, verbose)
            SELECT n.name, e.producer
            FROM products e
            INNER JOIN products_names n ON 
                n.product_id = e.product_id

            WHERE

                    n.name ilike '%eda%'                   

limit 20;
                                                                         QUERY PLAN                                                                          
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=58.34..260.70 rows=20 width=60) (actual time=0.257..0.458 rows=20 loops=1)
   Output: n.name, e.producer
   ->  Nested Loop  (cost=58.34..49564.37 rows=4893 width=60) (actual time=0.256..0.454 rows=20 loops=1)
         Output: n.name, e.producer
         ->  Bitmap Heap Scan on public.products_names n  (cost=57.92..14890.29 rows=4893 width=39) (actual time=0.245..0.333 rows=20 loops=1)
               Output: n.product_id, n.lang, n.name, n.name2, n.name3, n.products
               Recheck Cond: ((n.name)::text ~~* '%eda%'::text)
               Heap Blocks: exact=18
               ->  Bitmap Index Scan on products_names_name_gin_idx  (cost=0.00..56.70 rows=4893 width=0) (actual time=0.160..0.160 rows=797 loops=1)
                     Index Cond: ((n.name)::text ~~* '%eda%'::text)
         ->  Index Scan using products_pkey on public.products e  (cost=0.42..7.08 rows=1 width=29) (actual time=0.005..0.006 rows=1 loops=20)
               Output: e.producer, e.product_id
               Index Cond: (e.product_id = n.product_id)
 Planning time: 1.000 ms
 Execution time: 0.494 ms
(15 Zeilen)

Zeit: 2,563 ms

Possible duplicate of [PostgreSQL LIKE query performance variations](http://stackoverflow.com/questions/1566717/postgresql-like-query-performance-variations) — e4c5, Jul 16 '16 at 13:37

apm · Answer 1 · 2016-07-15T08:22:56.667

0

These are only a work around. You can push postgres to do with index by this.

SELECT * from
    (SELECT n.name, e.producer
        FROM products e
        INNER JOIN products_names n ON 
            n.product_id = e.product_id) a    
    WHERE
            name ilike '%eda%' or producer ilike '%eda%'

EDIT- Or try this one

SELECT * FROM
    (SELECT n.name, e.producer
        FROM products e
        INNER JOIN products_names n ON 
            n.product_id = e.product_id
        WHERE
                n.name ilike '%eda%'  )a
    WHERE a.producer ilike '%eda%'

edited Jul 15 '16 at 08:22

answered Jul 15 '16 at 07:12

apm

525
6
19

1

that does not work at all for me. it is not using GIN and it is even much much slower (3 seconds) probably because inner SELECT is basicly joining tables with all content and after that ILIKE is filtering. – Beginner99 Jul 15 '16 at 08:13
Can you try the second one. – apm Jul 15 '16 at 08:23
1

2nd one works and it is a nice idea, but it is delivering wrong results because inner select is filtering by name and producer is totally ignored. and if there is no producer with "eda" nothing is returned which is not correct – Beginner99 Jul 15 '16 at 08:31
sorry, It was 'or', I thought it was 'and' – apm Jul 15 '16 at 08:38
So work around with union is good one. Try to ask here. You will get a better solution http://irc.lc/freenode/postgresql/irctc@@@ – apm Jul 15 '16 at 08:39

gin not used on two columns at the same time in a JOIN

1 Answers1