explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bYI8

Settings
# exclusive inclusive rows x rows loops node
1. 2.441 2,940.012 ↓ 5,000.0 5,000 1

Subquery Scan on rankedquotes (cost=405.47..405.51 rows=1 width=32) (actual time=2,901.515..2,940.012 rows=5,000 loops=1)

  • Output: rankedquotes.quotevalue
  • Filter: (rankedquotes.rankvalue = 1)
  • Rows Removed by Filter: 31736
  • Buffers: shared hit=59040 read=18001, local hit=52, temp read=570 written=572
  • I/O Timings: read=2748.680
2. 31.166 2,937.571 ↓ 36,736.0 36,736 1

WindowAgg (cost=405.47..405.50 rows=1 width=64) (actual time=2,901.513..2,937.571 rows=36,736 loops=1)

  • Output: ROW(c.client, c.scope, e.provider, e.price_source, e.instrument_id, e.instrument_id_type, e.quote_type, e.field, q.effective_at, q.cut_label, q.value, q.units, q.lineage, q.uploaded_by, q.as_at, q.annulled_as_at)::quote, rank() OVER (?), q.effective_at, q.as_at, q.client_ref, q.entity_ref
  • Buffers: shared hit=59040 read=18001, local hit=52, temp read=570 written=572
  • I/O Timings: read=2748.680
3. 43.855 2,906.405 ↓ 36,736.0 36,736 1

Sort (cost=405.47..405.48 rows=1 width=198) (actual time=2,901.499..2,906.405 rows=36,736 loops=1)

  • Output: q.effective_at, q.as_at, q.client_ref, q.entity_ref, c.client, c.scope, e.provider, e.price_source, e.instrument_id, e.instrument_id_type, e.quote_type, e.field, q.cut_label, q.value, q.units, q.lineage, q.uploaded_by, q.annulled_as_at
  • Sort Key: q.client_ref, q.entity_ref, q.effective_at DESC, q.as_at DESC
  • Sort Method: external merge Disk: 4560kB
  • Buffers: shared hit=59040 read=18001, local hit=52, temp read=570 written=572
  • I/O Timings: read=2748.680
4. 10.525 2,862.550 ↓ 36,736.0 36,736 1

Nested Loop (cost=36.17..405.46 rows=1 width=198) (actual time=1.522..2,862.550 rows=36,736 loops=1)

  • Output: q.effective_at, q.as_at, q.client_ref, q.entity_ref, c.client, c.scope, e.provider, e.price_source, e.instrument_id, e.instrument_id_type, e.quote_type, e.field, q.cut_label, q.value, q.units, q.lineage, q.uploaded_by, q.annulled_as_at
  • Buffers: shared hit=59040 read=18001, local hit=52
  • I/O Timings: read=2748.680
5. 3.713 42.025 ↓ 5,000.0 5,000 1

Nested Loop (cost=35.60..396.85 rows=1 width=134) (actual time=1.493..42.025 rows=5,000 loops=1)

  • Output: c.client, c.scope, c.id, e.provider, e.price_source, e.instrument_id, e.instrument_id_type, e.quote_type, e.field, e.id
  • Inner Unique: true
  • Buffers: shared hit=19977 read=66, local hit=52
  • I/O Timings: read=2.036
6. 2.368 3.312 ↓ 116.3 5,000 1

Hash Join (cost=35.17..163.43 rows=43 width=106) (actual time=0.309..3.312 rows=5,000 loops=1)

  • Output: c.client, c.scope, c.id, qtf.provider, qtf.price_source, qtf.instrument_id, qtf.instrument_id_type, qtf.quote_type, qtf.field
  • Inner Unique: true
  • Hash Cond: ((qtf.client = c.client) AND (qtf.scope = c.scope))
  • Buffers: shared hit=12, local hit=52
7. 0.651 0.651 ↑ 1.0 5,000 1

Seq Scan on pg_temp_9.quotes_to_find qtf (cost=0.00..102.00 rows=5,000 width=51) (actual time=0.008..0.651 rows=5,000 loops=1)

  • Output: qtf.client, qtf.scope, qtf.provider, qtf.price_source, qtf.instrument_id, qtf.instrument_id_type, qtf.quote_type, qtf.field
  • Buffers: local hit=52
8. 0.175 0.293 ↑ 1.0 927 1

Hash (cost=21.27..21.27 rows=927 width=70) (actual time=0.293..0.293 rows=927 loops=1)

  • Output: c.client, c.scope, c.id
  • Buckets: 1024 Batches: 1 Memory Usage: 102kB
  • Buffers: shared hit=12
9. 0.118 0.118 ↑ 1.0 927 1

Seq Scan on quotes.client c (cost=0.00..21.27 rows=927 width=70) (actual time=0.003..0.118 rows=927 loops=1)

  • Output: c.client, c.scope, c.id
  • Buffers: shared hit=12
10. 35.000 35.000 ↑ 1.0 1 5,000

Index Scan using quotesmigration_entity_idx on quotes.entity e (cost=0.42..5.43 rows=1 width=64) (actual time=0.007..0.007 rows=1 loops=5,000)

  • Output: e.id, e.instrument_id, e.instrument_id_type, e.quote_type, e.provider, e.price_source, e.field
  • Index Cond: ((e.instrument_id = qtf.instrument_id) AND (e.instrument_id_type = qtf.instrument_id_type) AND (e.quote_type = qtf.quote_type) AND (e.provider = qtf.provider) AND (e.price_source = qtf.price_source) AND (e.field = qtf.field))
  • Buffers: shared hit=19965 read=66
  • I/O Timings: read=2.036
11. 2,810.000 2,810.000 ↓ 7.0 7 5,000

Index Scan using quotesmigration_bitemporally_uniq on quotes.quotes_migration q (cost=0.57..8.60 rows=1 width=72) (actual time=0.083..0.562 rows=7 loops=5,000)

  • Output: q.sequence_index, q.value, q.units, q.effective_at, q.as_at, q.annulled_as_at, q.uploaded_by, q.lineage, q.cut_label, q.entity_ref, q.client_ref
  • Index Cond: ((q.client_ref = c.id) AND (q.entity_ref = e.id) AND (q.effective_at <= '2019-11-09 00:00:00+00'::timestamp with time zone) AND (q.effective_at >= '0001-01-01 00:00:00+00'::timestamp with time zone) AND (q.as_at <= '2019-11-09 00:00:00+00'::timestamp with time zone))
  • Filter: ((q.annulled_as_at IS NULL) OR (q.annulled_as_at > '2019-11-09 00:00:00+00'::timestamp with time zone))
  • Buffers: shared hit=39063 read=17935
  • I/O Timings: read=2746.644
Planning time : 0.955 ms
Execution time : 2,941.003 ms