Skip to content

Instantly share code, notes, and snippets.

@baest

baest/gist:19da4b6d613f0b7d2e808d90b30b64d2

Created October 22, 2018 07:27
Show Gist options
  • Save baest/19da4b6d613f0b7d2e808d90b30b64d2 to your computer and use it in GitHub Desktop.
Save baest/19da4b6d613f0b7d2e808d90b30b64d2 to your computer and use it in GitHub Desktop.
Download ZIP
JOIN vs subselect
Raw
gistfile1.txt
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=276.706..3264.398 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.008..881.004 rows=5188155 loops=1) │
│ -> Hash (cost=21297.58..21297.58 rows=540610 width=4) (actual time=274.914..274.914 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.034..126.690 rows=511055 loops=1) │
│ Heap Fetches: 82329 │
│ Planning time: 0.341 ms │
│ Execution time: 3619.942 ms │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(9 rows)
Time: 3621.401 ms (00:03.621)
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.043..14568.910 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.146 ms │
│ Execution time: 14940.189 ms │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment