使用 postgresql 9.4,我们有一个简单的联系人表(id text not null(作为 pk(,blob json(来试验移植 couchdb CRM 数据库。我们最终将拆分为更多的列等,并为 rdbms 更按标识处理数据,但这不是暂时的重点。
大约有 100k 行。
我知道铁杆 postgresql 性能专家建议不要使用偏移量,但我可以接受一个小的性能损失(对低于 100 毫秒的任何内容感到满意(
SELECT id FROM couchcontacts OFFSET 10000 LIMIT 10
正如预期的那样需要<10ms
SELECT blob->>'firstName' FROM couchcontacts LIMIT 10
还需要 <10 毫秒(假设此处在 blob 列上解码操作 10 个 json(
SELECT blob->>'firstName' FROM couchcontacts OFFSET 10000 LIMIT 10
需要10秒以上!! 注意到偏移效率低下,为什么这可能导致 10,010 个 json 解码操作?由于投影没有副作用,我不明白为什么不能快速?
这是 json 功能对 postgres 来说相对较新的限制吗? 因此无法确定->>
操作器不会产生副作用?
有趣的是,将查询重写为此使其恢复到 10 毫秒以内
SELECT jsonblob->>'firstName' FROM couchdbcontacts WHERE id IN (SELECT id FROM couchcontacts OFFSET 10000 LIMIT 10)
有没有办法确保偏移量不会对偏移的记录进行解码?(即不执行选择投影(
"Limit (cost=1680.31..1681.99 rows=10 width=32) (actual time=12634.674..12634.842 rows=10 loops=1)"
" -> Seq Scan on couchcontacts (cost=0.00..17186.53 rows=102282 width=32) (actual time=0.088..12629.401 rows=10010 loops=1)"
"Planning time: 0.194 ms"
"Execution time: 12634.895 ms"
我运行了一些测试,我看到了类似的行为。其中每个在性能上都有非实质性差异:
-
select id
...... -
select indexed_field
...... -
select unindexed_field
...... -
select json_field
...... -
select *
......
但是,这确实显示了性能差异:
-
select json_field->>'key'
......
当json_field为空时,性能影响可以忽略不计。当它是空的时,它会非常轻微地降低事物。当它被填充时,它会明显退化。当字段加载更大的数据时,它会严重降级。
换句话说,Postgres 似乎想要反序列化它正在访问的每一行的 json 数据。(这可能是一个错误,并且极大地影响了RoR开发人员如何使用json。
Fwiw,我注意到重新排列查询以使其使用 CTE 将解决此问题:
with data as (
select * from table offset 10000 limit 10
)
select json_field->>'key' from data;
(它可能会得到一个比你突出显示的id IN (...)
查询稍微好一点的计划。