提高postgreSQL中简单左连接的性能

发布时间：2020-12-13 15:58:13 所属栏目：百科来源：网络整理

导读：我试图在postgreSQL数据库中的两个表之间进行左连接,并且发现它需要大约14分钟才能运行.从现有的SO帖子来看,似乎这种类型的连接应该在几秒钟的数量级,所以我想知道如何提高此连接的性能.我使用pgAdmin III在带有8 GB RAM的 Windows 8机器上运行64位postgreSQ

我试图在postgreSQL数据库中的两个表之间进行左连接,并且发现它需要大约14分钟才能运行.从现有的SO帖子来看,似乎这种类型的连接应该在几秒钟的数量级,所以我想知道如何提高此连接的性能.我使用pgAdmin III在带有8 GB RAM的 Windows 8机器上运行64位postgreSQL版本9.4.4.表结构如下：

表A：“parcels_qtr”：

parcel (text) | yr (int) | qtr (text) | lpid (pk,text) |

有1550万行,每列都被编入索引,“lpid”是主键.我还通过标准真空过程运行此表.

表B：“postalvac_qtr”：

parcel (text) | yr (int) | qtr (text) | lpid (pk,text) | vacCountY (int) |

有618,000条记录,除“vacCountY”之外的所有字段都被编入索引,“lpid”是主键.这也经历了标准的真空过程.

运行数据输出时,大约需要14分钟.使用explain(analyze,buffers)运行时需要花一点多时间.第一个问题 – 这种性能差异完全可归因于打印数据还是其他相关问题？

第二个问题,我可以将运行时间缩短到几秒钟吗？

这是我的SQL代码：

EXPLAIN (ANALYZE,BUFFERS)
select a.parcel,a.lpid,a.yr,a.qtr,b."vacCountY"
from parcels_qtr as a
left join postalvac_qtr as b
on a.lpid = b.lpid;

这是我的解释声明的结果：https://explain.depesz.com/s/uKkK

我对postgreSQL很新,所以耐心和解释会非常感激！

解决方法

你要求DB做很多工作.只看一下解释计划,它是：

>读入整个表格(postalvac_qtr)
>基于lpid构建哈希
>读入另一个更大的表格(parcels_qtr)
>哈希每个15MM lpid,并将它们与现有哈希表匹配

这些表有多大？您可以通过发出以下命令来检查

SELECT pg_size_pretty(pg_relation_size('parcels_qtr'));

我几乎可以肯定,这个散列连接会溢出到磁盘,以及它的结构方式(“给我这两个表中的所有数据”),但它绝不可能.

指数没有帮助,也没有.只要你要求整个表,使用索引只会使事情变慢 – 无论如何postgres必须遍历整个表,所以它也可以发出顺序扫描.

至于为什么查询具有与解释分析不同的性能,我怀疑你是正确的. 1-向您的客户端发送15M行,以及2-尝试显示它们的组合将导致实际查询之外的显着减速.

所以你能对它做点啥？

首先,这个查询试图做什么？您希望多久获取这两个表中的所有数据,完全未经过滤？如果它很常见,您可能需要考虑回到需求阶段并找出解决该需求的另一种方法(例如,获取给定年份和季度的所有数据是否合理？).如果它不常见(例如,每日出口),则1-14分钟可能没问题.

其次,你应该确保你的表没有膨胀.如果您在表上遇到重大更新或删除流量,则会随着时间的推移而增加. autovacuum守护进程可以帮助解决这个问题,但偶尔发出一个真空吸尘器也会有所帮助.

第三,您可以尝试调整数据库配置.在postgresql.conf中,有一些参数可用于服务器可用于磁盘高速缓存的预期RAM量,以及服务器可用于排序或连接的RAM量(在它溢出到磁盘之前).通过修改这些参数,您可以提高速度.

第四,您可能想要重新访问您的架构.您是否希望将年份和季度作为两个单独的列,或者您是否会更好地使用日期类型的单个列？你想要一个文本键,或者你是否会更好地使用bigint(串行或从文本列派生),这可能会更快加入？两个表中实际上是否需要parcel,yr和qtr字段,还是它们在一个表中重复数据？

无论如何,我希望这会有所帮助.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!