Slow query with left outer join and is null condition

Question

I've got a simple query (postgresql if that matters) that retrieves all items for some_user excluding the ones she has on her wishlist:

select i.* 
from core_item i 
left outer join core_item_in_basket b on (i.id=b.item_id and b.user_id=__some_user__)
where b.on_wishlist is null;

The above query runs in ~50000ms (yep, the number is correct). If I remove the "b.on_wishlist is null" condition or make it "b.on_wishlist is not null", the query runs in some 50ms (quite a change).

The query has more joins and conditions but this is irrelevant as only this one slows it down.

Some info on the database size:

• core_items has ~ 10.000 records
• core_user has ~5.000 records
• core_item_in_basket has ~2.000
• records (of which some 50% has on_wishlist = true, the rest is null)

I don't have any indexes (except for ids and foreign keys) on those two tables.

The question is: what should I do to make this run faster? I've got a few ideas myself to check out this evening, but I'd like you guys to help if possible, as well.

Thanks!

Answer 1

try using not exists:

select i.* 
from   core_item i 
where  not exists (select * from core_item_in_basket b where i.id=b.item_id and b.user_id=__some_user__)

Answer 2

Have you tried adding an index on on_wishlist?

It seems that this column needs to be checked for every row in the query. If your tables are that big, this might have quite a significant impact on the query speed.

As you put the on_wishlist condition in the where clause, which will cause it (depending on the what the query planer decides) to be evaluated after the join has been performed, that comparison has to be done for potentially every row resulting from the join. Both the core_items and core_item_in_basket tables are pretty big, and you don't have an index for that column, so there is very little for the query optimizer to do, which probably leads to the excessive query time.

The size of core_user should have no influence (as it is not referenced in the query).

Answer 3

You might want to explain more about the purpose of this query - as some techniques make and some don't make sense, depending on use case.

How often are you running it?

Is it run for only 1 user, or you run it for all users in some kind of loop?

Do: explain analyze and put the output on explain.depesz.com so you will see why it is so slow.

Answer 4

Sorry for adding 2nd answer, but stackoverflow doesn't let me format comments properly, and since formatting is essential, I have to post answer.

Couple of options:

1. CREATE INDEX q ON core_item_in_basket (user_id, item_id) WHERE on_wishlist is null;
2. same index, but change order of columns in it.
3. SELECT i.* FROM core_item i WHERE i.id not in (select item_id FROM core_item_in_basket WHERE on_wishlist is null AND user_id = __some_user__); (this query can benefit from index from point #1, but will not benefit from index #2.
4. SELECT * from core_item where id in (select id from core_item EXCEPT select item_id FROM core_item_in_basket WHERE on_wishlist is null AND user_id = __some_user__);

Let us know the results :)