explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gmE3

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.012 94,879.719 ↑ 1.0 1 1

Aggregate (cost=2,950,529.75..2,950,529.76 rows=1 width=8) (actual time=94,879.719..94,879.719 rows=1 loops=1)

  • Buffers: shared hit=304844 read=1351945 dirtied=4463 written=164
2.          

CTE p

3. 46,675.361 46,675.361 ↓ 0.0 0 1

Index Scan using events_status_created_at_scheduled_at_idx on events events_1 (cost=0.57..1,476,992.06 rows=1 width=32) (actual time=46,675.361..46,675.361 rows=0 loops=1)

  • Index Cond: ((status = ANY ('{DONE,NEW}'::event_status[])) AND (created_at > (now() - '06:00:00'::interval)))
  • Filter: (((entity)::text = 'posting'::text) AND ((data ->> 'delivery_type'::text) = ANY ('{PickPoint,PVZ}'::text[])) AND ((data ->> 'state_dst'::text) = 'posting_in_pickup_point'::text) AND (date >= (now() - '7 days'::interval)) AND (date <= (now() - '6 days'::interval)))
  • Rows Removed by Filter: 1242876
  • Buffers: shared hit=158327 read=667813 dirtied=4253 written=146
4. 22.569 94,879.707 ↓ 0.0 0 1

Nested Loop Semi Join (cost=0.57..1,473,537.50 rows=78 width=8) (actual time=94,879.707..94,879.707 rows=0 loops=1)

  • Join Filter: ((events.data ->> 'shipment_id'::text) = p.shipment_id)
  • Buffers: shared hit=304844 read=1351945 dirtied=4463 written=164
5. 48,177.198 48,177.198 ↓ 306.0 47,730 1

Index Scan using events_status_created_at_scheduled_at_idx on events (cost=0.57..1,473,533.59 rows=156 width=1,062) (actual time=883.109..48,177.198 rows=47,730 loops=1)

  • Index Cond: ((status = ANY ('{DONE,NEW}'::event_status[])) AND (created_at > (now() - '06:00:00'::interval)))
  • Filter: (((entity)::text = 'shipment'::text) AND (date <= now()) AND ((data ->> 'state_dst'::text) = 'shipment_in_pickup_point'::text) AND (date >= (now() - '6 days'::interval)))
  • Rows Removed by Filter: 1195146
  • Buffers: shared hit=146517 read=684132 dirtied=210 written=18
6. 46,679.940 46,679.940 ↓ 0.0 0 47,730

CTE Scan on p (cost=0.00..0.02 rows=1 width=32) (actual time=0.978..0.978 rows=0 loops=47,730)

  • Buffers: shared hit=158327 read=667813 dirtied=4253 written=146
Planning time : 0.398 ms
Execution time : 94,879.924 ms