explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 26HV

Settings
# exclusive inclusive rows x rows loops node
1. 35.071 167,003.447 ↑ 2.4 3,221 1

Sort (cost=19,575.70..19,595.16 rows=7,783 width=104) (actual time=166,989.419..167,003.447 rows=3,221 loops=1)

  • Sort Key: (min(date_trunc('second'::text, timezone('MSK'::text, timezone('UTC'::text, pmt.event_created_at)))))
  • Sort Method: quicksort Memory: 562kB
  • Buffers: shared hit=933,905 read=571,083
  • I/O Timings: read=244,516.498
2. 0.000 166,968.376 ↑ 2.4 3,221 1

Finalize HashAggregate (cost=18,994.85..19,072.68 rows=7,783 width=104) (actual time=166,949.914..166,968.376 rows=3,221 loops=1)

  • Group Key: s.shop_id, s.party_id, s.location_url
  • Buffers: shared hit=933,902 read=571,083
  • I/O Timings: read=244,516.498
3. 20,989.074 168,016.720 ↑ 7.2 3,221 1

Gather (cost=18,450.04..18,761.36 rows=23,349 width=104) (actual time=139,471.971..168,016.720 rows=3,221 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=933,902 read=571,083
  • I/O Timings: read=244,516.498
4. 24,655.365 147,027.646 ↑ 9.7 805 4 / 4

Partial HashAggregate (cost=18,350.04..18,427.87 rows=7,783 width=104) (actual time=147,022.363..147,027.646 rows=805 loops=4)

  • Group Key: s.shop_id, s.party_id, s.location_url
  • Buffers: shared hit=933,902 read=571,083
  • I/O Timings: read=244,516.498
5. 36,021.468 122,372.281 ↓ 3.9 2,679,499 4 / 4

Nested Loop Left Join (cost=66.13..6,202.87 rows=694,124 width=104) (actual time=43.948..122,372.281 rows=2,679,499 loops=4)

  • Buffers: shared hit=933,902 read=571,083
  • I/O Timings: read=244,516.498
6. 83.368 89.212 ↑ 3.4 805 4 / 4

Parallel Bitmap Heap Scan on shop s (cost=65.44..873.23 rows=2,728 width=96) (actual time=42.842..89.212 rows=805 loops=4)

  • Recheck Cond: current
  • Filter: ((category_id <> ALL ('{1,2}'::integer[])) AND ((party_id)::text <> '16c67b2a-ac2c-4b2f-ab00-20b2a436d4f5'::text))
  • Rows Removed by Filter: 1,425
  • Heap Blocks: exact=565
  • Buffers: shared hit=3,176
7. 5.843 5.843 ↓ 1.0 8,922 1 / 4

Bitmap Index Scan on shop_party_id_partial (cost=0.00..63.33 rows=8,869 width=0) (actual time=23.372..23.374 rows=8,922 loops=1)

  • Buffers: shared hit=119
8. 86,261.601 86,261.601 ↓ 118.8 3,327 3,221 / 4

Index Only Scan using payment_shop_id_party_id_event_created_at_partial on payment pmt (cost=0.69..1.67 rows=28 width=78) (actual time=0.149..107.124 rows=3,327 loops=3,221)

  • Index Cond: ((shop_id = (s.shop_id)::text) AND (party_id = (s.party_id)::text) AND (event_created_at >= '2019-08-31 21:00:00'::timestamp without time zone) AND (event_created_at <= '2019-10-31 21:00:00'::timestamp without time zone))
  • Heap Fetches: 500,567
  • Buffers: shared hit=930,726 read=571,083
  • I/O Timings: read=244,516.498
Planning time : 87.615 ms
Execution time : 168,120.962 ms