Skip to content

Instantly share code, notes, and snippets.

View baest's full-sized avatar

Martin Frausing baest

5 followers · 0 following
View GitHub Profile
@baest
baest / gist:644306377583606279c4a71bff0a82c4
Created October 22, 2018 07:47
JOIN vs subselect
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=540
@baest
baest / gist:19da4b6d613f0b7d2e808d90b30b64d2
Created October 22, 2018 07:27
JOIN vs subselect
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=540
@baest
baest / flac2mp3
Created January 27, 2011 21:32
use strict;
use warnings;
use File::Find::Rule;
my %id_names = (
artist => 'ta',
year => 'ty',
date => 'ty', # alias
comment => 'tc',
album => 'tl',