Using postgresql 9.4, we have a simple contact table with (id text not null (as pk), blob json) to experiment with porting the couchdb crm database. In the end, we will divide into more columns, etc., and process the data more ideally for rdbms, but even more so for now.
There are about 100 thousand lines.
I know that hardcore post-congress experts don't recommend using bias, but I can accept a slight performance limitation (happy with something less than 100 ms)
SELECT id FROM couchcontacts OFFSET 10000 LIMIT 10
As expected, <10ms
SELECT blob->>'firstName' FROM couchcontacts LIMIT 10
Also takes <10 ms (suppose 10 json decode ops on blob column here)
SELECT blob->>'firstName' FROM couchcontacts OFFSET 10000 LIMIT 10
Takes more than 10 seconds! There is sideshift inefficiency, why is this supposedly causing 10,010 json decode ops? Since the projection has no side effects, I don’t understand why it cannot be quick?
Is this limitation of json functionality relatively new to postgres? and, therefore, the inability to determine the operand ->>
gives no side effects?
An interesting rewrite of the request for this returns it less than 10 million seconds
SELECT jsonblob->>'firstName' FROM couchdbcontacts WHERE id IN (SELECT id FROM couchcontacts OFFSET 10000 LIMIT 10)
Is there a way to provide bias, not json decodes the biased records? (i.e. do not perform the selected projection)
"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"