Use join for single relations

[benjie]

Instead of

select ..., (select json_build_object(...) from relation where ...) alias from source

do

select ..., (json_build_object(...)) alias from source left join relation on (...)

[benjie]

(But check it gives better performance first)

[baest]

Example of an query where the performance between subselect and join matters (https://pastebin.com/raw/gw7DmXuD):

`db=[db]# EXPLAIN ANALYZE SELECT f.id, d.id FROM feature f JOIN dna d ON f.dna_id = d.id; ┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Hash Join (cost=28055.20..200791.46 rows=4993569 width=8) (actual time=290.061..3481.233 rows=5188155 loops=1) │ │ Hash Cond: (f.dna_id = d.id) │ │ -> Seq Scan on feature f (cost=0.00..104074.69 rows=4993569 width=8) (actual time=0.010..1051.609 rows=5188155 loops=1) │ │ -> Hash (cost=21297.58..21297.58 rows=540610 width=4) (actual time=288.138..288.138 rows=511055 loops=1) │ │ Buckets: 1048576 Batches: 1 Memory Usage: 26159kB │ │ -> Index Only Scan using dna_pkey on dna d (cost=0.42..21297.58 rows=540610 width=4) (actual time=0.040..132.826 rows=511055 loops=1) │ │ Heap Fetches: 82329 │ │ Planning time: 0.426 ms │ │ Execution time: 3840.655 ms │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (9 rows)

Time: 3842.244 ms (00:03.842) db=[db]# EXPLAIN ANALYZE SELECT f.id, (SELECT id FROM dna WHERE id = f.dna_id) as dna_id FROM feature f ; ┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Seq Scan on feature f (cost=0.00..22288004.97 rows=4993569 width=8) (actual time=0.047..14790.370 rows=5188155 loops=1) │ │ SubPlan 1 │ │ -> Index Only Scan using dna_pkey on dna (cost=0.42..4.44 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=5188155) │ │ Index Cond: (id = f.dna_id) │ │ Heap Fetches: 1540356 │ │ Planning time: 0.235 ms │ │ Execution time: 15173.165 ms │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘`

[benjie]

V5 does this via optimization of PgSelectStep.