sql - PostgreSQL 连接性能

Question

我在 PostgreSQL 9.3 数据库中有两个表:

TableA(ID numeric, VALUE numeric(5,4)) - about 1milion records.

TableB(ID numeric, field2 .. fieldN) - about 5milion records.

我正在尝试按 ID 字段在 TableA 和 TableB 之间进行简单的 JOIN，按字段 VALUE 进行过滤。尽管 VALUE 可以有 4 位小数，但应用的过滤器只能有两位小数。

我的 SQL 看起来像这样(0.45 是过滤器参数，范围从 0.00 到 1.00):

SELECT A.ID, A.VALUE, B.a_lot_of_fields_here
FROM TableA A, TableB B
WHERE A.ID = B.ID
and A.VALUE >= 0.45

两个 ID(A.ID，B.ID)都是 PK。我想知道我是否可以做些什么来加快 SQL 的速度。有什么建议吗？

这是查询解释计划:

"Hash Join  (cost=22205.73..310550.04 rows=1167395 width=105)"
"  Hash Cond: ((a.id)::text = (b.id)::text)"
"  ->  Seq Scan on tablea a  (cost=0.00..140163.94 rows=3557794 width=80)"
"  ->  Hash  (cost=17404.18..17404.18 rows=248284 width=25)"
"        ->  Bitmap Heap Scan on tableb b  (cost=4652.63..17404.18 rows=248284 width=25)"
"              Recheck Cond: (value >= 0.1)"
"              ->  Bitmap Index Scan on index_test  (cost=0.00..4590.56 rows=248284 width=0)"
"                    Index Cond: (value >= 0.1)"

还有一个带有分析/缓冲区的

"Hash Join  (cost=22205.73..315312.78 rows=1398289 width=109) (actual time=2065.165..12794.984 rows=1267024 loops=1)"
"  Hash Cond: ((a.id)::text = (b.id)::text)"
"  Buffers: shared hit=177 read=71080 written=47217, temp read=47454 written=47424"
"  ->  Seq Scan on tablea a  (cost=0.00..107631.74 rows=4261474 width=84) (actual time=0.014..2815.098 rows=3557794 loops=1)"
"        Buffers: shared hit=175 read=64842 written=43965"
"  ->  Hash  (cost=17404.18..17404.18 rows=248284 width=25) (actual time=2047.615..2047.615 rows=248617 loops=1)"
"        Buckets: 2048  Batches: 16  Memory Usage: 901kB"
"        Buffers: shared hit=2 read=6238 written=3252, temp written=1319"
"        ->  Bitmap Heap Scan on tableb b  (cost=4652.63..17404.18 rows=248284 width=25) (actual time=491.395..1914.202 rows=248617 loops=1)"
"              Recheck Cond: (value >= 0.1)"
"              Buffers: shared hit=2 read=6238 written=3252"
"              ->  Bitmap Index Scan on index_test  (cost=0.00..4590.56 rows=248284 width=0) (actual time=448.286..448.286 rows=248617 loops=1)"
"                    Index Cond: (value >= 0.1)"
"                    Buffers: shared read=682"
"Total runtime: 12905.306 ms"

(On explain.depesz.com)

现在我正在处理 A.ID 和 B.ID 字段:它们是文本字段...我将把文本编码成整数，看看使用这种方法可以提高多少性能。

Postgres 配置内容:

我查看了 postresql.conf。我的所有参数都具有默认值:( - 我没有调整它们的知识-。只有一个被编辑(由系统管理员):

shared_buffers = 128MB

Answer 1

如果 A.VALUE >= 0.45 的数量或记录相对较小(比如少于总计数的 5%)，A.VALUE 上的索引可以加快查询速度。 (A.VALUE, A.ID) 上的复合索引也可以允许仅在表 A 上进行索引扫描。