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

概述我试图在postgreSQL数据库中的两个表之间进行左连接,并且发现它需要大约14分钟才能运行.从现有的SO帖子来看,似乎这种类型的连接应该在几秒钟的数量级,所以我想知道如何提高此连接的性能.我使用pgAdmin III在带有8 GB RAM的 Windows 8机器上运行64位postgreSQL版本9.4.4.表结构如下： 表A：“parcels_qtr”： parcel (text) | y 我试图在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 aleft join postalvac_qtr as bon 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字段,还是它们在一个表中重复数据？

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

总结

以上是内存溢出为你收集整理的提高postgreSQL中简单左连接的性能全部内容，希望文章能够帮你解决提高postgreSQL中简单左连接的性能所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。