sql - 如何从 SQL 查询中快速获得结果

Question

我有一个 Postgre SQL 数据库表，其中包含超过 500 万个条目。还有一个包含 100,000 个条目的 CSV 文件。

我需要运行查询以从数据库中获取与 CSV 文件数据相关的数据。

但是根据大家的理解和我自己的经验，这种查询需要很长时间才能完成。 (超过 6 小时，按照我的猜测)

那么根据最新的发现和工具，我们是否有更好、更快速的解决方案来执行相同的任务？

Answer 1

快速 channel :创建一个匹配 CSV 文件结构的 temporary table(为方便起见，可能使用现有表格作为模板)并使用 COPY :

批量加载

CREATE TEMP TABLE tmp(email text);

COPY tmp FROM 'path/to/file.csv';
ANALYZE tmp;                       -- do that for bigger tables!

我假设 CSV 中的电子邮件是唯一的，您没有指定。如果不是，让它们独一无二:

CREATE TEMP TABLE tmp0
SELECT DISTINCT email
FROM   tmp
ORDER  BY email;  -- ORDER BY cheap in combination with DISTINCT ..
                  -- .. may or may not improve performance additionally.

DROP TABLE tmp;
ALTER TABLE tmp0 RENAME TO tmp;

索引

<罢工>对于您的特定情况，电子邮件上的唯一 索引是有序的。在加载和清理数据后 创建索引会更有效。这样你也可以防止 COPY 在应该有 dupes 的情况下以独特的违规行为摆脱困境:

CREATE UNIQUE INDEX tmp_email_idx ON tmp (email);

<罢工>

转念一想，如果您所做的只是更新大表，那么您根本不需要临时表上的索引。它将被顺序阅读。

Yes DB table is indexed using primary key.

本例中唯一相关的索引:

CREATE INDEX tbl_email_idx ON tbl (email);

如果可能，制作那个 CREATE UNIQUE INDEX ...。

更新

要按照您稍后的评论中的详细信息更新您的表格:

UPDATE tbl t
SET    ...
FROM   tmp 
WHERE  t.email = tmp.email;

所有这些都可以很容易地包装到 plpgsql 或 sql 函数中。
请注意，如果您想参数化文件名，COPY 需要在 plpgsql 函数中使用 EXECUTE 的动态 SQL。

默认情况下，临时表会在 session 结束时自动删除。
相关回答:
How to bulk insert only new rows in PostreSQL