具有 SELECT DISTINCT 和 Join 的慢速 Postgres 查询

Question

我们有一个从Oracle迁移而来的Postgres DB。有一个查询在 Posgres 中很慢(8-11 秒)，但在 Oracle 中很快(1-2 秒)。它涉及一个SELECT DISTINCT，其他几个线程在 Postgres 中将其标记为问题：1、2。 我们可以做一些简单的事情，比如添加索引或其他东西，而无需进行重大重写？

查询将表NED_PERSON_T与自身联接，以获取其UNIQUEIDENTIFIER是子NIHSERVAO中指定的父值的所有行。这是一个SELECT DISTINCT如下。

select
distinct nedperson0_.ID as ID1_21_,
nedperson0_.BUILDINGNAME as BUILDING2_21_,
nedperson0_.CREATED_DATE as CREATED_4_21_,
nedperson0_.CURRENT_FLAG as CURRENT_5_21_,
/* ... + 30 other columns ... */
from
NED_PERSON_T nedperson0_ 
inner join
NED_PERSON_T nedperson1_ 
on (
nedperson1_.NIHSERVAO=nedperson0_.UNIQUEIDENTIFIER 
and nedperson0_.CURRENT_FLAG='Y' 
and nedperson1_.CURRENT_FLAG='Y'
) 
order by
nedperson0_.NIHMIXCASESN,
nedperson0_.MIXCASE_GIVENNAME

解释分析给出了这样的报告：

Unique  (cost=362155.58..390755.66 rows=119167 width=1197) (actual time=8722.383..11202.495 rows=838 loops=1)
Sort Key: nedperson0_.nihmixcasesn, nedperson0_.mixcase_givenname, nedperson0_.id, nedperson0_.buildingname, nedperson0_.created_date, nedperson0_.description, ... ... )
Sort Method: external merge  Disk: 78016kB
->  Gather  (cost=103675.72..227466.24 rows=119167 width=1197) (actual time=2485.391..3100.424 rows=97678 loops=1)
Workers Planned: 2
Workers Launched: 2
->  Parallel Hash Join  (cost=102675.72..214549.54 rows=49653 width=1197) (actual time=2477.318..2880.555 rows=32559 loops=3)
Hash Cond: (nedperson1_.nihservao = nedperson0_.uniqueidentifier)
->  Parallel Index Scan using ned_person_t_current_flag_idx on ned_person_t nedperson1_  (cost=0.43..93430.46 rows=58867 width=11) (actual time=7.536..815.779 rows=46800 loops=3)
Index Cond: (current_flag = 'Y'::bpchar)
->  Parallel Hash  (cost=93430.46..93430.46 rows=58867 width=1154) (actual time=1620.284..1620.285 rows=46800 loops=3)
Buckets: 4096  Batches: 64  Memory Usage: 1792kB
->  Parallel Index Scan using ned_person_t_current_flag_idx on ned_person_t nedperson0_  (cost=0.43..93430.46 rows=58867 width=1154) (actual time=4.602..1226.402 rows=46800 loops=3)
Index Cond: (current_flag = 'Y'::bpchar)
Planning Time: 3.986 ms
Execution Time: 11218.403 ms

索引：

CREATE INDEX ned_person_t_ao_test1 ON ned.ned_person_t USING btree (nihmixcasesn, mixcase_givenname, uniqueidentifier)
CREATE INDEX ned_person_t_current_flag_idx ON ned.ned_person_t USING btree (current_flag)
CREATE INDEX ned_person_t_id_idx ON ned.ned_person_t USING btree (id)
CREATE INDEX ned_person_t_mdslink_idx ON ned.ned_person_t USING btree (nihmdslinktoadnih)
CREATE INDEX ned_person_t_nedid_idx ON ned.ned_person_t USING btree (uniqueidentifier)
CREATE INDEX ned_person_t_orgacronym_idx ON ned.ned_person_t USING btree (nihorgacronym)
CREATE INDEX ned_person_t_orgstat_idx ON ned.ned_person_t USING btree (organizationalstat)
CREATE UNIQUE INDEX ned_person_t_pk ON ned.ned_person_t USING btree (id)
CREATE INDEX ned_person_t_sac_idx ON ned.ned_person_t USING btree (nihsac)
CREATE INDEX ned_person_t_temp_idx ON ned.ned_person_t USING btree (nihservao, organizationalstat, nihsac)
CREATE INDEX ned_person_t_temp_idx2 ON ned.ned_person_t USING btree (lower(nihmdslinktoadnih), current_flag, nihservao, organizationalstat, nihsac)

NED_PERSON_T统计：

select count(*) from ned_person_t

总数 =1243733

select count(*) from ned_person_t where current_flag = 'Y';

CURRENT_FLAG = Y：142540

select count(*) from ned_person_t where nihservao is not null;

与非空的NIHSERVAO：644852

select count(distinct nihservao) from ned_person_t;

独特的尼赫斯陶：928

Answer 1

要使 EXISTS 查询快速运行，您需要的是(current_flag, nihservao)上的索引(或者可能是 current_flag 上过滤的 nihservao 上的部分索引)，以便它可以使用仅索引扫描来获取 nihservao 值，而无需跳转到每行表的随机部分。这需要保持桌子的吸尘良好才能有效。

如果许多其他查询也只关注当前数据，则按current_flag对数据进行分区可能是有意义的，以使相关数据更加密集。 不过，这比仅仅创建索引更激烈。

Answer 2

您的EXISTS查询看起来不错。

1243733 行中只有 142540 行有CURRENT_FLAG='Y'.
只有 644852 行有nihservao IS NOT NULL.(与第一行的组合很重要。
没有uniqueidentifier IS NULL的情况。

如果定期查询该条件，则部分索引应该非常有效。我建议以下两个：

CREATE INDEX ON ned.ned_person_t (nihservao)
WHERE current_flag = 'Y' AND nihservao IS NOT NULL;

由于您只需要返回uniqueidentifier和fullname(就像您后来评论的那样) - 如果fullname不是太大 -覆盖索引可以提供更多帮助。(要求桌子有足够的真空

。

CREATE INDEX ON ned.ned_person_t (uniqueidentifier) INCLUDE (fullname)
WHERE current_flag = 'Y';

例：

具有主键和外键的查询

• 是否比仅具有主键的查询运行得更快？

在使用它时，检查是否所有许多索引实际上(仍然)被使用。这个是完全多余的，删除它：

CREATE INDEX ned_person_t_id_idx ON ned.ned_person_t USING btree (id)

唯一索引ned_person_t_pk已经涵盖了id(实际上可能是一个PRIMARY KEY，而不仅仅是一个唯一的索引)。

最终的排序操作不太重要，因为只剩下844行。

Answer 3

作为更多信息，根据上面的建议，我 (1) 使用 EXISTS 重写了查询，(2) 将MEM_SIZE增加到 100 MB。但这仍然需要 7 秒。以下是解释。如果我之后按顺序重新运行此查询，由于缓存，花费的时间要少得多，但第一次运行大约需要 7 秒，如下所示。

更新：存在重写确实将时间缩短了一半，达到大约 4 秒。这次运行并不具有代表性，我们尝试了更多(断开连接/重新连接后)，EXISTS 平均好了大约一半。

查询：

select
nedperson0_.ID as ID1_21_,
nedperson0_.BUILDINGNAME as BUILDING2_21_,
nedperson0_.CREATED_DATE as CREATED_4_21_,
nedperson0_.CURRENT_FLAG as CURRENT_5_21_,
/* ... + 30 other columns ... */
from
NED_PERSON_T nedperson0_ 
where
exists (select uniqueidentifier from ned_person_t nedperson1_ 
where nihservao = nedperson0_.uniqueidentifier and nedperson1_.current_flag = 'Y'
)
and 
nedperson0_.current_flag = 'Y'
order by
nedperson0_.NIHMIXCASESN,
nedperson0_.MIXCASE_GIVENNAME

解释分析：

Sort  (cost=139290.39..139293.75 rows=1344 width=1195) (actual time=7627.351..7627.441 rows=844 loops=1)
Sort Method: quicksort  Memory: 882kB
->  Nested Loop  (cost=97523.64..139220.55 rows=1344 width=1195) (actual time=5112.442..7621.895 rows=844 loops=1)
->  HashAggregate  (cost=97523.21..97531.04 rows=783 width=11) (actual time=5108.419..5109.041 rows=847 loops=1)
Group Key: nedperson1_.nihservao
->  Index Scan using ned_person_t_current_flag_idx on ned_person_t nedperson1_  (cost=0.43..97172.64 rows=140228 width=11) (actual time=2.376..4999.905 rows=142540 loops=1)
Index Cond: (current_flag = 'Y'::bpchar)
->  Index Scan using ned_person_t_nedid_idx on ned_person_t nedperson0_  (cost=0.43..53.21 rows=2 width=1152) (actual time=2.964..2.964 rows=1 loops=847)
Index Cond: (uniqueidentifier = nedperson1_.nihservao)
Filter: (current_flag = 'Y'::bpchar)
Rows Removed by Filter: 23
Planning Time: 10.259 ms
Execution Time: 7627.670 ms