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=933905 read=571083
  • I/O Timings: read=244516.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=933902 read=571083
  • I/O Timings: read=244516.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=933902 read=571083
  • I/O Timings: read=244516.498
4. 24,655.365 147,027.646 ↑ 9.7 805 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=933902 read=571083
  • I/O Timings: read=244516.498
5. 122,175.945 122,372.281 ↓ 3.9 2,679,499 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=933902 read=571083
  • I/O Timings: read=244516.498
6. 65.838 89.212 ↑ 3.4 805 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: 1425
  • Heap Blocks: exact=565
  • Buffers: shared hit=3176
7. 23.374 23.374 ↓ 1.0 8,922 1

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. 107.124 107.124 ↓ 118.8 3,327 3,221

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: 500567
  • Buffers: shared hit=930726 read=571083
  • I/O Timings: read=244516.498
Planning time : 87.615 ms
Execution time : 168,120.962 ms