explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VEH

Settings
# exclusive inclusive rows x rows loops node
1. 2.572 421.334 ↓ 5,000.0 5,000 1

Subquery Scan on rankedquotes (cost=5,666.71..5,666.76 rows=1 width=32) (actual time=366.202..421.334 rows=5,000 loops=1)

  • Filter: (rankedquotes.rankvalue = 1)
  • Rows Removed by Filter: 31515
  • Buffers: shared hit=66994, local hit=52, temp read=530 written=531
2. 37.922 418.762 ↓ 36,515.0 36,515 1

WindowAgg (cost=5,666.71..5,666.75 rows=1 width=184) (actual time=366.200..418.762 rows=36,515 loops=1)

  • Buffers: shared hit=66994, local hit=52, temp read=530 written=531
3. 247.026 380.840 ↓ 36,515.0 36,515 1

Sort (cost=5,666.71..5,666.72 rows=1 width=192) (actual time=366.182..380.840 rows=36,515 loops=1)

  • Sort Key: q.client, q.scope, q.provider, q.price_source, q.instrument_id, q.instrument_id_type, q.quote_type, q.field, q.effective_at DESC, q.as_at DESC
  • Sort Method: external merge Disk: 4240kB
  • Buffers: shared hit=66994, local hit=52, temp read=530 written=531
4. 8.299 133.814 ↓ 36,515.0 36,515 1

Nested Loop (cost=0.82..5,666.70 rows=1 width=192) (actual time=0.046..133.814 rows=36,515 loops=1)

  • Buffers: shared hit=66994, local hit=52
5. 0.515 0.515 ↓ 2.7 5,000 1

Seq Scan on quotes_to_find qtf (cost=0.00..70.20 rows=1,820 width=200) (actual time=0.007..0.515 rows=5,000 loops=1)

  • Buffers: local hit=52
6. 125.000 125.000 ↓ 7.0 7 5,000

Index Scan using quotes_bitemporally_uniq on quotes q (cost=0.82..3.07 rows=1 width=192) (actual time=0.018..0.025 rows=7 loops=5,000)

  • Index Cond: ((client = qtf.client) AND (scope = qtf.scope) AND (provider = qtf.provider) AND (price_source = qtf.price_source) AND (instrument_id = qtf.instrument_id) AND (instrument_id_type = qtf.instrument_id_type) AND (quote_type = qtf.quote_type) AND (field = qtf.field) AND (effective_at <= '2019-11-09 00:00:00+00'::timestamp with time zone) AND (effective_at >= '0001-01-01 00:00:00+00'::timestamp with time zone) AND (as_at <= '2019-11-09 00:00:00+00'::timestamp with time zone))
  • Filter: ((annulled_as_at IS NULL) OR (annulled_as_at > '2019-11-09 00:00:00+00'::timestamp with time zone))
  • Buffers: shared hit=66994
Planning time : 0.390 ms
Execution time : 422.242 ms