Postgresql – Postgres not using indexes when ORing across join

indexjoin;postgresql

I'm doing a query with a join on two tables, where I need to do an ILIKE filter on a field in each of the tables with wildcards at both ends, and it should return a row if either of them match.

Approx:

SELECT * FROM "tableA" LEFT OUTER JOIN "tableB" ON 
"tableA"."tableB_id" = "tableB"."id" 
WHERE "tableA"."name" ILIKE '%rich%' OR "tableB"."name" ILIKE '%rich%';

As both of these tables are quite large I decided to try using trigram indexes to speed things up. However, this didn't appear to help, and having done an EXPLAIN ANALYZE I discovered that the indexes are not used. They are used if the filters are combined with AND instead of OR, and they are used if I do an OR between two indexed columns in the same table, just not with an OR between indexed columns in different tables.

Anyone have any ideas why?

Best Answer

It's sometimes useful to rewrite a query with OR conditions as a UNION (ALL) query. This usually leads to a different execution plan which may be more efficient. Testing will identify which of the two methods/plans is more efficient in each case (eg. if the trigram indexes are used for both parts of the union).

The rewrite:

SELECT * 
FROM "tableA" AS a 
    LEFT OUTER JOIN "tableB" AS b
    ON a."tableB_id" = b."id" 
WHERE a."name" ILIKE '%rich%'

UNION ALL

SELECT * 
FROM "tableA" AS a 
    INNER JOIN "tableB" AS b
    ON a."tableB_id" = b."id" 
WHERE b."name" ILIKE '%rich%'
  AND (a."name" IS NULL OR a."name" NOT ILIKE '%rich%')
 ;

Related Solutions

Sql-server – Full outer joins, coalescing, indexes and views

Try this

select a.Date, a.Foo, b.Bar
from TableA a LEFT JOIN TableB b ON a.Date = b.Date
UNION
select b.Date, a.Foo, b.Bar
from TableB b LEFT JOIN TableA a ON a.Date = b.Date

Strictly speaking, the query above does not quite have the same semantics as the original full join, with respect to duplicates. A correct transformation of full join is to a left join union all anti-semi-join:

SELECT 
    TA.TheDate, 
    TA.Foo, 
    TB.Bar
FROM dbo.TableA AS TA
LEFT JOIN dbo.TableB AS TB 
    ON TB.TheDate = TA.TheDate
UNION ALL
SELECT 
    TB.TheDate, 
    Foo = NULL, 
    TB.Bar 
FROM dbo.TableB AS TB
WHERE NOT EXISTS 
(
    SELECT NULL
    FROM dbo.TableA AS TA 
    WHERE TA.TheDate = TB.TheDate
);

This second rewrite will also facilitate the use of an index on TheDate columns.

Mysql – Why indexes are not speeding up multiple join query

@Jehad Keriaki provided the answer. The lastContacts table's primary key column, ContactID, was defined as a string while the same column in each of the other tables was an integer. Joining tables on that column caused implicit conversion and resulted in poor performance.

Best Answer

Related Solutions

Sql-server – Full outer joins, coalescing, indexes and views

Mysql – Why indexes are not speeding up multiple join query

Related Question