PostgreSQL is not selecting index when OR is used

user_508330 0
Viewed 36

I have a strange situation when PostgreSQL is ignoring index when 'OR' statement is used when table is joined.

I have a setup where data is divided by half where foreign key is used and another part is having 'lose reference'. Table names are used just for the demonstration (synthetic example), but the logic is next: When 'order' cannot be exactly mapped we need to use another text fields to find a match.

I have tried next queries:

Select DISTICNT product.id, client.id, order.id
From clients as client
CROSS JOIN products as product
Left JOIN orders as order on 
  (
    order.product_id_fk = products.id AND
    order.user_fk = client.id
  )
  OR
  (
    order.product_id_fk = products.id AND
    order.user_fk is null AND
    order.user_group = client.user_group -- text
  )
WHERE product.id = 1 # param

and this query

Select DISTICNT product.id, client.id, order.id
From clients as client
CROSS JOIN products as product
Left JOIN orders as order on 
order.product_id_fk = products.id AND
  (
    order.user_fk = client.id
    OR 
    ( 
      order.user_fk is null AND 
      order.user_group = client.user_group -- this search should be applied only when fk is not set
    )
  )
WHERE product.id = 1 # param

For both queries index is ignored and query is taking 12 seconds to perform.

At the same time, next query is working ultra fast using both indexes and PostgreSQL is choosing indexes correctly:

Select DISTICNT client.id, COALESCE(order1.id, order2.id)
From clients as client
CROSS JOIN products
Left JOIN orders as order on 
  order.product_id_fk = products.id AND order.user_fk = client.id
Left JOIN orders as order2 on 
  order.product_id_fk = products.id AND order.user_fk is null AND order.user_group = client.user_group

WHERE product.id = 1

I have the following indices:

Create Index on orders(product_id_fk, user_fk) where user_fk is not null

Create Index on orders(product_id_fk, user_group) where user_fk is null

Create Index on orders(product_id_fk, user_fk, user_group) where user_fk is null

I have tried as well to use index without condition, but it was also ignored. 'Explain' is just showing that Seq Scan will be used for first two queries.

Would appreciate any ideas why indexes for the first two queries indexes are ignored and how to analyze it better.

indexing postgresql
1 Answers

Creating separate indexes instead of combined indexes might help. For eg.

CREATE INDEX ON orders(product_id_fk);
CREATE INDEX ON orders(user_group);
CREATE INDEX ON orders(user_fk);

Also, where conditions can be checked while creating indexes if needed.

user_195933050
Related