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

Question

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

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

Place: id, name (String), select (Boolean)
Bookmark: id, user (Integer), place (Integer)
User: id, name (String)

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?

Laurenz Albe · Accepted Answer · 2019-08-21 12:12:03Z

3

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;

answered Aug 21, 2019 at 12:12

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related