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

Question

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:

If I try with single condition, without OR, it works (uses index)
If I replace OR with AND - it works
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?

I've run into the same problem; it's an odd behaviour. I'm surprised probably because I don't understand DB internals very well. Could it be that the query planner thinks OR will result in too many rows and thinks a sequential scan is better? I can see that it is definitely over-estimating number of rows. — tayfun
– tayfun, Commented Oct 5, 2022 at 18:41

Lukasz Szozda · Accepted Answer · 2018-03-05 19:56:08Z

1

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%';

edited Mar 5, 2018 at 19:56

answered Mar 5, 2018 at 19:37

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

xersiee Over a year ago

Yes, we were thinking about this, but like I wrote query is pretty complex and I'm afraid duplicating it for every field will have bad impact on both performance and code readability. But If I dont find any solution I would probably go for that.

Lukasz Szozda Over a year ago

@xersiee You could avoid duplication to some degree utilising CTE/LATERAL JOINS.

xersiee Over a year ago

I'll see what I can do, thanks for your help. Anyway, its interesting (and annoying!) that it is not working:)

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related