CODEWITHSUNDEEP
sqlpostgresqlindexing
xersiee
xersiee

Reputation: 4462

PostgreSQL - gin indexes on fields from many tables and OR conditions

Here's my very simplified business requirement (using PostgreSQL 10.1):

There are two tables order and customer which are retrieved from db together using join. Both tables contain fields which have to be used for filtering by using ILIKE keyword. Then I have two gin indexes to improve querying performance:

CREATE INDEX order_type ON orderd USING gin (type gin_trgm_ops);
CREATE INDEX customer_name ON customer USING gin (name gin_trgm_ops);

Now I want my query to look like this:

SELECT * FROM orderd o JOIN customer c ON c.id=o.customer_id
WHERE o.type ILIKE '%user_query%' OR c.name ILIKE '%user_query%';

I would expect it to use indexes but EXPLAIN ANALYZE shows something different. These are my observations after many trials:

  1. If I try with single condition, without OR, it works (uses index)
  2. If I replace OR with AND - it works
  3. If I have two indexes on the same table it works (for both OR and AND)

But it looks like it cannot work if I want to use indexes from different tables and OR conditions. Why is that? Is there any way to make it work?

My actual business scenario is much more complicated, there is more than two tables and more than two fields, there are other filters, there are also some statistics calculated in the query so I have to implement all the filtering in single query. Any ideas how I can do that?

Upvotes: 3

Views: 1224

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

You could try to use UNION/UNION ALL:

SELECT *
FROM orderd o 
JOIN customer c
  ON c.id=o.customer_id
WHERE o.type ILIKE '%user_query%'
UNION ALL
SELECT *
FROM orderd o 
JOIN customer c
  ON c.id=o.customer_id
WHERE c.name ILIKE '%user_query%';

I've assumed that OR EXPANSION will work similar to Oracle.

To avoid duplication (to some degree) you could use CTE:

WITH cte AS (
    SELECT *
    FROM orderd o 
    JOIN customer c
      ON c.id=o.customer_id
)
SELECT *
FROM cte
WHERE type ILIKE '%user_query%'   --predicate pushdown should do the job
UNION ALL
SELECT *
FROM cte
WHERE name name ILIKE '%user_query%';

Upvotes: 1

Related Questions