使用NOT IN时PostgreSQL查询速度很慢 [英] PostgreSQL query is slow when using NOT IN

问题描述

我有一个PostgreSQL函数，可以将查询结果返回到pgadmin结果网格中。
内部，这是一个简单的函数，它使用 dblink 连接到另一个数据库并返回查询，以便我可以简单地运行

I have a PostgreSQL function that returns a query result to pgadmin results grid REALLY FAST. Internally, this is a simple function that uses a dblink to connect to another database and does a query return so that I can simply run

SELECT * FROM get_customer_trans();

它的运行就像基本的表查询一样。

And it runs just like a basic table query.

问题是当我使用 NOT IN 子句时。所以我想运行以下查询，但是要花很多时间：

The issue is when I use the NOT IN clause. So I want to run the following query, but it takes forever:

SELECT * FROM get_customer_trans()
WHERE user_email NOT IN 
    (SELECT do_not_email_address FROM do_not_email_tbl);

如何加快速度？在这种情况下，有没有比 NOT IN 子句更快的东西了？

How can I speed this up? Anything faster than a NOT IN clause for this scenario?

推荐答案

get_customer_trans（）不是表-可能是一些存储过程，因此查询并不是很简单。您需要查看此存储过程的实际作用，以了解为什么它可能运行缓慢。

get_customer_trans() is not a table - probably some stored procedure, so query is not really trivial. You'd need to look at what this stored procedure really does to understand why it might work slow.

但是，不管存储过程的行为如何，添加以下索引都应该有助于lot：

However, regardless of stored procedure behavior, adding following index should help a lot:

CREATE INDEX do_not_email_tbl_idx1
    ON do_not_email_tbl(do_not_email_address);

该索引使 NOT IN 查询可以快速进行返回答案。但是，已知 NOT IN 在较旧的PostgreSQL版本中存在问题-因此请确保您至少运行PostgreSQL 9.1或更高版本。

This index lets NOT IN query to quickly return answer. However, NOT IN is known to have issues in older PostgreSQL versions - so make sure that you are running at least PostgreSQL 9.1 or later.

更新。尝试将查询更改为：

UPDATE. Try to change your query to:

SELECT t.*
FROM get_customer_trans() AS t
WHERE NOT EXISTS (
    SELECT 1
    FROM do_not_email_tbl
    WHERE do_not_email_address = t.user_email
    LIMIT 1
)

此查询不使用 NOT IN ，并且应该可以快速运行。
我认为在PostgreSQL 9.2中，此查询应该与使用 NOT IN 的查询一样快。

This query does not use NOT IN, and should work fast. I think that in PostgreSQL 9.2 this query should work as fast as one with NOT IN though.

这篇关于使用NOT IN时PostgreSQL查询速度很慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！