我有两个表,产品和products_names.
我在两个表中使用GIN索引在两列中进行ILIKE匹配,但仅当我在一列上执行ILIKE时才使用GIN.
我通过UNION做了一个解决方法,但我想知道它为什么不能像我认为的那样工作.
两个列,n.name和e.producer都是VARCHAR,它们上面有GIN索引：
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.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
解决方法
这些只是一种解决方法.你可以推动postgres做索引.
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%'
