explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aimj

Settings
# exclusive inclusive rows x rows loops node
1. 2.277 815.968 ↑ 1.0 1 1

Nested Loop (cost=8,337.9..8,470.48 rows=1 width=139) (actual time=796.009..815.968 rows=1 loops=1)

  • Buffers: shared hit=59025 read=93740 written=1, temp read=268 written=269
2. 1.059 778.760 ↓ 2,687.0 2,687 1

Nested Loop (cost=8,337.34..8,462.75 rows=1 width=187) (actual time=687.236..778.76 rows=2,687 loops=1)

  • Buffers: shared hit=48505 read=90770 written=1, temp read=268 written=269
3. 4.283 753.518 ↓ 2,687.0 2,687 1

Nested Loop (cost=8,336.91..8,452.87 rows=1 width=48) (actual time=687.172..753.518 rows=2,687 loops=1)

  • Buffers: shared hit=39655 read=87110 written=1, temp read=268 written=269
4. 12.045 711.211 ↓ 679.0 9,506 1

Merge Join (cost=8,336.49..8,347.26 rows=14 width=32) (actual time=687.013..711.211 rows=9,506 loops=1)

  • Buffers: shared hit=3928 read=84746 written=1, temp read=268 written=269
5. 71.296 621.286 ↓ 77.5 83,928 1

Sort (cost=4,244.55..4,247.26 rows=1,083 width=16) (actual time=611.817..621.286 rows=83,928 loops=1)

  • Sort Key: dv2.nodeid
  • Sort Method: external merge Disk: 2144kB
  • Buffers: shared hit=1684 read=70445 written=1, temp read=268 written=269
6. 533.624 549.990 ↓ 77.5 83,931 1

Bitmap Heap Scan on datagramvariant dv2 (cost=39.53..4,189.96 rows=1,083 width=16) (actual time=23.062..549.99 rows=83,931 loops=1)

  • Heap Blocks: exact=38200 lossy=33605
  • Buffers: shared hit=1684 read=70445 written=1
7. 16.366 16.366 ↓ 77.5 83,931 1

Bitmap Index Scan on datagramvariant_name_values_idx (cost=0..39.25 rows=1,083 width=0) (actual time=16.366..16.366 rows=83,931 loops=1)

  • Index Cond: (((dv2.name)::text = 'SEASON_NUMBER'::text) AND ((dv2."values" ->> 0) = '1'::text))
  • Buffers: shared hit=1 read=323
8. 8.600 77.880 ↓ 17.6 18,359 1

Sort (cost=4,091.94..4,094.54 rows=1,043 width=16) (actual time=75.177..77.88 rows=18,359 loops=1)

  • Sort Key: dv3.nodeid
  • Sort Method: quicksort Memory: 1568kB
  • Buffers: shared hit=2244 read=14301
9. 66.163 69.280 ↓ 16.3 17,049 1

Bitmap Heap Scan on datagramvariant dv3 (cost=39.12..4,039.65 rows=1,043 width=16) (actual time=5.436..69.28 rows=17,049 loops=1)

  • Heap Blocks: exact=16459
  • Buffers: shared hit=2244 read=14301
10. 3.117 3.117 ↓ 16.3 17,049 1

Bitmap Index Scan on datagramvariant_name_values_idx (cost=0..38.85 rows=1,043 width=0) (actual time=3.117..3.117 rows=17,049 loops=1)

  • Index Cond: (((dv3.name)::text = 'EPISODE_NUMBER'::text) AND ((dv3."values" ->> 0) = '1'::text))
  • Buffers: shared read=86
11. 38.024 38.024 ↓ 0.0 0 9,506

Index Scan using node_pkey on node_data n (cost=0.42..7.54 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=9,506)

  • Index Cond: (n.id = dv2.nodeid)
  • Filter: ((n.pid = '5aabb5d0-eec9-11e6-a951-8f956a8d5610'::uuid) AND content.has_any_scopes(n.id, n.type, n.subtype, n.category, (COALESCE(current_setting('my.scopes'::text, true), '{}'::text))::character varying[]))
  • Buffers: shared hit=35727 read=2364
12. 24.183 24.183 ↑ 1.0 1 2,687

Index Scan using externalid_nodeid_idx on externalid extid (cost=0.42..9.87 rows=1 width=139) (actual time=0.007..0.009 rows=1 loops=2,687)

  • Index Cond: (extid.nodeid = n.id)
  • Filter: ((extid.source)::text = 'CONTENT_ID'::text)
  • Buffers: shared hit=8850 read=3660
13. 34.931 34.931 ↓ 0.0 0 2,687

Index Scan using datagramvariant_nodeid_name_idx on datagramvariant dv1 (cost=0.56..7.72 rows=1 width=16) (actual time=0.013..0.013 rows=0 loops=2,687)

  • Index Cond: ((dv1.nodeid = extid.nodeid) AND ((dv1.name)::text = 'PROVIDER_SERIES_ID'::text))
  • Filter: ((dv1."values" ->> 0) = 'iYEQZ2rcf32XLQAVbZkS59'::text)
  • Buffers: shared hit=10520 read=2970