带有左外部联接且为空条件的查询缓慢 [英] Slow query with left outer join and is null condition

问题描述

我有一个简单的查询（如果需要的话，是PostgreSQL），为some_user检索所有项，但不包括她在愿望清单中的项：

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;

上面的查询在〜50000ms内运行（是的，数字正确）。
如果删除 b.on_wishlist为空条件或使其变为 b.on_wishlist不为空，查询将在大约50毫秒内运行（相当大的变化）。

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有〜10.000条记录


• core_user有〜5.000条记录


• core_item_in_basket有〜2.000


• 条记录（其中约50％的记录有
  on_wishlist = true，其余为空）

• 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)

在这两个表上我没有任何索引（除了id和外键）。

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.

谢谢！

推荐答案

很抱歉添加第二个答案，但是stackoverflow不允许我格式化注释 正确，并且由于格式化是必不可少的，因此我必须发布答案。

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

选项对：

1. 创建索引q ON core_item_in_basket（user_id，item_id）WHERE on_wishlist为空；


2. 相同索引，但更改其中的列顺序。


3. 在i。* FROM core_item中选择i.id，不在其中（在on_wishlist为null且从user_id = __some_user__中选择item_id从core_item_in_basket中选择）； （此查询可以从点＃1的索引中受益，但不能从索引＃2的内容中受益。


4. SELECT * from core_item，其中id在其中（从core_item中选择id，除了从core_item_in_basket中选择item_id，在这里on_wishlist为null AND user_id = __some_user __）;

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__);

让我们知道结果：）

这篇关于带有左外部联接且为空条件的查询缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！