explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gnVc : Optimization for: plan #gmE3

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.004 47,614.230 ↑ 1.0 1 1

Aggregate (cost=2,959,564.97..2,959,564.98 rows=1 width=8) (actual time=47,614.230..47,614.230 rows=1 loops=1)

  • Buffers: shared hit=145032 read=679210 dirtied=7131 written=396
2.          

CTE p

3. 47,614.220 47,614.220 ↓ 0.0 0 1

Index Scan using events_status_created_at_scheduled_at_idx on events (cost=0.57..1,481,515.87 rows=1 width=32) (actual time=47,614.220..47,614.220 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: 1240695
  • Buffers: shared hit=145032 read=679210 dirtied=7131 written=396
4. 0.003 47,614.226 ↓ 0.0 0 1

Nested Loop (cost=0.57..1,478,049.10 rows=1 width=8) (actual time=47,614.226..47,614.226 rows=0 loops=1)

  • Join Filter: ((e.data ->> 'shipment_id'::text) = p.shipment_id)
  • Buffers: shared hit=145032 read=679210 dirtied=7131 written=396
5. 47,614.223 47,614.223 ↓ 0.0 0 1

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

  • Buffers: shared hit=145032 read=679210 dirtied=7131 written=396
6. 0.000 0.000 ↓ 0.0 0

Index Scan using events_status_created_at_scheduled_at_idx on events e (cost=0.57..1,478,046.73 rows=157 width=1,062) (never executed)

  • 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)))
Planning time : 0.491 ms
Execution time : 47,614.458 ms