postgresql - 在日期字段上使用索引时，PostgreSQL 表导出查询的性能低下

Question

我在使用 postgresql 表时遇到问题。该表有 14 列，其中 2 列的索引如下：

       Column        | Type | Modifiers 
---------------------+------+-----------
 u_id                | text | not null
 p_date              | date | not null
...

Indexes:
    "unq_uid_pdate" UNIQUE CONSTRAINT, btree (u_id, p_date)
    "pdate_idx" btree (p_date)

表的大小约为 50 亿行，在 u_id 中总共有大约 1000 万个不同的值，每个 u_id 在 p_date 中平均有大约 500 个不同的值。

当我尝试通过 u_id 导出或不使用任何过滤器时，性能很好，可以达到每秒 400K 行的速度，而不会随着时间的推移而显着下降。

psql "..." -c "\\copy (SELECT u_id, p_date FROM l_nights) to stdout with csv header" | pv --line-mode --rate > /dev/null

[ 468k/s]
[ 485k/s]
[ 497k/s]
[ 435k/s]

但是，如果我要导出特定 p_date 的大约 1000 万行，性能很差，从每秒几千行开始，几分钟后就停止了。添加limit行为是相同的：

psql "..." -c "\\copy (SELECT u_id, p_date FROM l_nights where p_date = '2020-06-01' limit 50000) to stdout with csv header" | pv --line-mode --rate > /dev/null

[1.09k/s]
[1.28k/s]
[1.48k/s]
[3.01k/s]
[ 954 /s]
[1.40k/s]
[ 918 /s]
[ 849 /s]
[ 383 /s]
[ 364 /s]
[ 358 /s]
[0.00 /s]
[ 170 /s]
[0.00 /s]

该查询使用正确的索引，基于解释响应：

explain analyse select u_id, p_date from l_nights where p_date = '2020-06-01' limit 50000;

QUERY PLAN                                                                           
---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.72..158646.09 rows=50000 width=15) (actual time=9.672..207430.728 rows=50000 loops=1)
   ->  Index Scan using pdate_idx on l_nights  (cost=0.72..233913736.58 rows=73722207 width=15) (actual time=9.671..207396.204 rows=50000 loops=1)
         Index Cond: (p_date = '2020-06-01'::date)
 Planning Time: 0.182 ms
 Execution Time: 207451.360 ms

根据当前的性能，在我看来，搜索是在进行全面扫描，即使该列已编入索引。其他导出策略（例如在 node.js 中获取 postgresql 游标）的行为也相同。

我究竟做错了什么？

标签： postgresqlperformanceindexingexportbigdata