CODEWITHSUNDEEP
postgresql
delCano
delCano

Reputation: 385

Postgres optimizing in an "A OR exists(B)" query

I'm having a lot of trouble with a particular case in my Postgres optimization.

Essentially, I have three tables, which will simplify as:

The Place table has several million rows (and growing), but a relatively small amount of them has select as true.

I have several indexes on these tables, obviously on all id, plus a partial one on place where "select"=true, and a unique one on the bookmark (user, place) combos. There are more, but I think they're not relevant here.

When I do a query of the type:

SELECT * 
FROM place 
WHERE "select" 
LIMIT 10;

it takes 3ms.

When I do a query of the type:

SELECT * 
FROM place 
WHERE exists (SELECT id 
              FROM bookmark 
              WHERE user IN (1,2,3,4) 
                AND bookmark.place = place.id) 
LIMIT 10;

it's also blazing fast.

However, if I do an OR on both conditions, like so:

SELECT * 
FROM place 
WHERE "select" 
   OR exists (SELECT id 
              FROM bookmark 
              WHERE user IN (1,2,3,4) 
                AND bookmark.place = place.id) 
LIMIT 10;

it slows down to over 1s.

Besides doing two queries in my code and combining the results, is there any way I can optimize this?

Upvotes: 1

Views: 331

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247865

The old problem, OR is a performance killer.

Use UNION:

(SELECT * FROM place
 WHERE select
 LIMIT 10)
UNION
(SELECT * FROM place
 WHERE exists (SELECT 1 FROM bookmark
               WHERE user IN (1,2,3,4)
                 AND bookmark.place = place.id)
 LIMIT 10)
LIMIT 10;

Upvotes: 3

Related Questions