Skip to content

Instantly share code, notes, and snippets.

@paramsingh

paramsingh/query_plans.md

Created May 16, 2019 18:34
Show Gist options
  • Save paramsingh/58fc124de20490d0313be6d234bcbc9c to your computer and use it in GitHub Desktop.
Save paramsingh/58fc124de20490d0313be6d234bcbc9c to your computer and use it in GitHub Desktop.
Download ZIP
Raw
query_plans.md

SELECT JOIN everything without greater than

acousticbrainz=> explain analyze select ll.gid::text, llj.data::text, ll.id from lowlevel as ll join lowlevel_json as llj on llj.id = ll.id left join highlevel as hl on ll.id = hl.id where hl.mbid is null limit 100;
                                                           QUERY PLAN

--------------------------------------------------------------------------------------------------------
-------------------------
 Limit  (cost=29.72..52.30 rows=1 width=68) (actual time=0.093..0.093 rows=0 loops=1)
   ->  Nested Loop  (cost=29.72..52.30 rows=1 width=68) (actual time=0.092..0.092 rows=0 loops=1)
         ->  Hash Right Join  (cost=29.58..51.20 rows=5 width=20) (actual time=0.091..0.091 rows=0 loops
=1)
               Hash Cond: (hl.id = ll.id)
               Filter: (hl.mbid IS NULL)
               Rows Removed by Filter: 2
               ->  Seq Scan on highlevel hl  (cost=0.00..19.20 rows=920 width=20) (actual time=0.004..0.
005 rows=2 loops=1)
               ->  Hash  (cost=18.70..18.70 rows=870 width=20) (actual time=0.022..0.022 rows=2 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                     ->  Seq Scan on lowlevel ll  (cost=0.00..18.70 rows=870 width=20) (actual time=0.01
1..0.013 rows=2 loops=1)
         ->  Index Scan using lowlevel_json_pkey on lowlevel_json llj  (cost=0.14..0.22 rows=1 width=36)
 (never executed)
               Index Cond: (id = ll.id)
 Planning time: 2.176 ms
 Execution time: 0.265 ms
(14 rows)

SELECT JOIN everything with greater than and no order by


acousticbrainz=> explain analyze select ll.gid::text, llj.data::text, ll.id from lowlevel as ll join lowlevel_json as llj on llj.id = ll.id left join highlevel as hl on ll.id = hl.id where hl.mbid is null and ll.id >= 2 limit 100;
                                                                   QUERY PLAN

--------------------------------------------------------------------------------------------------------
----------------------------------------
 Limit  (cost=23.79..45.94 rows=1 width=68) (actual time=0.085..0.085 rows=0 loops=1)
   ->  Nested Loop  (cost=23.79..45.94 rows=1 width=68) (actual time=0.084..0.084 rows=0 loops=1)
         ->  Hash Right Join  (cost=23.65..45.27 rows=2 width=20) (actual time=0.083..0.083 rows=0 loops
=1)
               Hash Cond: (hl.id = ll.id)
               Filter: (hl.mbid IS NULL)
               Rows Removed by Filter: 1
               ->  Seq Scan on highlevel hl  (cost=0.00..19.20 rows=920 width=20) (actual time=0.008..0.
010 rows=2 loops=1)
               ->  Hash  (cost=20.02..20.02 rows=290 width=20) (actual time=0.050..0.050 rows=1 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                     ->  Bitmap Heap Scan on lowlevel ll  (cost=6.40..20.02 rows=290 width=20) (actual t
ime=0.019..0.020 rows=1 loops=1)
                           Recheck Cond: (id >= 2)
                           Heap Blocks: exact=1
                           ->  Bitmap Index Scan on lowlevel_pkey  (cost=0.00..6.33 rows=290 width=0) (a
ctual time=0.011..0.011 rows=1 loops=1)
                                 Index Cond: (id >= 2)
         ->  Index Scan using lowlevel_json_pkey on lowlevel_json llj  (cost=0.14..0.33 rows=1 width=36)
 (never executed)
               Index Cond: (id = ll.id)
 Planning time: 0.715 ms
 Execution time: 0.199 ms
(18 rows)

join everything with greater than and order by

acousticbrainz=> explain analyze select ll.gid::text, llj.data::text, ll.id from lowlevel as ll join lowlevel_json as llj on llj.id = ll.id left join highlevel as hl on ll.id = hl.id where hl.mbid is null and ll.id >= 2 order by ll.id limit 100;
                                                                      QUERY PLAN

--------------------------------------------------------------------------------------------------------
----------------------------------------------
 Limit  (cost=45.95..45.96 rows=1 width=68) (actual time=0.140..0.140 rows=0 loops=1)
   ->  Sort  (cost=45.95..45.96 rows=1 width=68) (actual time=0.138..0.138 rows=0 loops=1)
         Sort Key: ll.id
         Sort Method: quicksort  Memory: 25kB
         ->  Nested Loop  (cost=23.79..45.94 rows=1 width=68) (actual time=0.100..0.101 rows=0 loops=1)
               ->  Hash Right Join  (cost=23.65..45.27 rows=2 width=20) (actual time=0.099..0.099 rows=0
 loops=1)
                     Hash Cond: (hl.id = ll.id)
                     Filter: (hl.mbid IS NULL)
                     Rows Removed by Filter: 1
                     ->  Seq Scan on highlevel hl  (cost=0.00..19.20 rows=920 width=20) (actual time=0.0
09..0.011 rows=2 loops=1)
                     ->  Hash  (cost=20.02..20.02 rows=290 width=20) (actual time=0.046..0.046 rows=1 lo
ops=1)
                           Buckets: 1024  Batches: 1  Memory Usage: 9kB
                           ->  Bitmap Heap Scan on lowlevel ll  (cost=6.40..20.02 rows=290 width=20) (ac
tual time=0.033..0.034 rows=1 loops=1)
                                 Recheck Cond: (id >= 2)
                                 Heap Blocks: exact=1
                                 ->  Bitmap Index Scan on lowlevel_pkey  (cost=0.00..6.33 rows=290 width
=0) (actual time=0.022..0.022 rows=1 loops=1)
                                       Index Cond: (id >= 2)
               ->  Index Scan using lowlevel_json_pkey on lowlevel_json llj  (cost=0.14..0.33 rows=1 wid
th=36) (never executed)
                     Index Cond: (id = ll.id)
 Planning time: 0.837 ms
 Execution time: 0.275 ms
(21 rows)

no lowlevel_json join without order by

acousticbrainz=> explain analyze select ll.id from lowlevel as ll left join highlevel as hl on ll.id = hl.id where hl.mbid is null and ll.id >= 0 limit 100;
                                                                QUERY PLAN

--------------------------------------------------------------------------------------------------------
----------------------------------
 Limit  (cost=23.65..45.27 rows=2 width=4) (actual time=0.071..0.071 rows=0 loops=1)
   ->  Hash Right Join  (cost=23.65..45.27 rows=2 width=4) (actual time=0.069..0.069 rows=0 loops=1)
         Hash Cond: (hl.id = ll.id)
         Filter: (hl.mbid IS NULL)
         Rows Removed by Filter: 2
         ->  Seq Scan on highlevel hl  (cost=0.00..19.20 rows=920 width=20) (actual time=0.007..0.009 ro
ws=2 loops=1)
         ->  Hash  (cost=20.02..20.02 rows=290 width=4) (actual time=0.037..0.037 rows=2 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               ->  Bitmap Heap Scan on lowlevel ll  (cost=6.40..20.02 rows=290 width=4) (actual time=0.0
29..0.031 rows=2 loops=1)
                     Recheck Cond: (id >= 0)
                     Heap Blocks: exact=1
                     ->  Bitmap Index Scan on lowlevel_pkey  (cost=0.00..6.33 rows=290 width=0) (actual
time=0.019..0.019 rows=2 loops=1)
                           Index Cond: (id >= 0)
 Planning time: 0.443 ms
 Execution time: 0.154 ms
(15 rows)

no lowlevel json join with order by

acousticbrainz=> explain analyze select ll.id from lowlevel as ll left join highlevel as hl on ll.id = hl.id where hl.mbid is null and ll.id >= 0 order by ll.id limit 100;
                                                                   QUERY PLAN

--------------------------------------------------------------------------------------------------------
----------------------------------------
 Limit  (cost=45.28..45.29 rows=2 width=4) (actual time=0.145..0.145 rows=0 loops=1)
   ->  Sort  (cost=45.28..45.29 rows=2 width=4) (actual time=0.143..0.143 rows=0 loops=1)
         Sort Key: ll.id
         Sort Method: quicksort  Memory: 25kB
         ->  Hash Right Join  (cost=23.65..45.27 rows=2 width=4) (actual time=0.091..0.091 rows=0 loops=
1)
               Hash Cond: (hl.id = ll.id)
               Filter: (hl.mbid IS NULL)
               Rows Removed by Filter: 2
               ->  Seq Scan on highlevel hl  (cost=0.00..19.20 rows=920 width=20) (actual time=0.008..0.
009 rows=2 loops=1)
               ->  Hash  (cost=20.02..20.02 rows=290 width=4) (actual time=0.028..0.029 rows=2 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                     ->  Bitmap Heap Scan on lowlevel ll  (cost=6.40..20.02 rows=290 width=4) (actual ti
me=0.016..0.018 rows=2 loops=1)
                           Recheck Cond: (id >= 0)
                           Heap Blocks: exact=1
                           ->  Bitmap Index Scan on lowlevel_pkey  (cost=0.00..6.33 rows=290 width=0) (a
ctual time=0.007..0.007 rows=2 loops=1)
                                 Index Cond: (id >= 0)
 Planning time: 0.438 ms
 Execution time: 0.236 ms
(18 rows)

no lowlevel json join without order by

acousticbrainz=> explain analyze select ll.id from lowlevel as ll left join highlevel as hl on ll.id = hl.id where hl.mbid is null and ll.id >= 0 limit 100;
                                                                QUERY PLAN

--------------------------------------------------------------------------------------------------------
----------------------------------
 Limit  (cost=23.65..45.27 rows=2 width=4) (actual time=0.061..0.061 rows=0 loops=1)
   ->  Hash Right Join  (cost=23.65..45.27 rows=2 width=4) (actual time=0.060..0.060 rows=0 loops=1)
         Hash Cond: (hl.id = ll.id)
         Filter: (hl.mbid IS NULL)
         Rows Removed by Filter: 2
         ->  Seq Scan on highlevel hl  (cost=0.00..19.20 rows=920 width=20) (actual time=0.007..0.008 ro
ws=2 loops=1)
         ->  Hash  (cost=20.02..20.02 rows=290 width=4) (actual time=0.031..0.031 rows=2 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               ->  Bitmap Heap Scan on lowlevel ll  (cost=6.40..20.02 rows=290 width=4) (actual time=0.0
24..0.026 rows=2 loops=1)
                     Recheck Cond: (id >= 0)
                     Heap Blocks: exact=1
                     ->  Bitmap Index Scan on lowlevel_pkey  (cost=0.00..6.33 rows=290 width=0) (actual
time=0.016..0.016 rows=2 loops=1)
                           Index Cond: (id >= 0)
 Planning time: 0.354 ms
 Execution time: 0.131 ms
(15 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment