explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FxUB

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 8,283.676 ↑ 1.0 20 1

Limit (cost=963,857.75..963,857.80 rows=20 width=1,076) (actual time=8,283.672..8,283.676 rows=20 loops=1)

2.          

CTE mix_orders

3. 4.595 6,040.735 ↓ 1.4 54,358 1

Append (cost=0.00..748,873.59 rows=39,894 width=12) (actual time=2,330.123..6,040.735 rows=54,358 loops=1)

4. 3,679.441 3,679.441 ↓ 1.6 48,057 1

Seq Scan on fake_orders (cost=0.00..182,737.20 rows=29,127 width=12) (actual time=2,330.121..3,679.441 rows=48,057 loops=1)

  • Filter: (((date)::date >= ('2019-11-02 00:00:00+07'::timestamp with time zone)::date) AND ((date)::date <= ('2019-11-02 23:59:59+07'::timestamp with time zone)::date))
  • Rows Removed by Filter: 5777180
5. 2,356.699 2,356.699 ↑ 1.7 6,301 1

Seq Scan on orders orders_1 (cost=0.00..565,737.45 rows=10,767 width=12) (actual time=197.556..2,356.699 rows=6,301 loops=1)

  • Filter: (((processed_at)::date >= ('2019-11-02 00:00:00+07'::timestamp with time zone)::date) AND ((processed_at)::date <= ('2019-11-02 23:59:59+07'::timestamp with time zone)::date))
  • Rows Removed by Filter: 2148223
6. 32.723 8,283.668 ↑ 2.5 20 1

Sort (cost=214,984.16..214,984.28 rows=50 width=1,076) (actual time=8,283.667..8,283.668 rows=20 loops=1)

  • Sort Key: (((mix_orders.processed_at)::date + (orders.processed_at - ((orders.processed_at)::date)::timestamp with time zone)))
  • Sort Method: top-N heapsort Memory: 35kB
7. 129.717 8,250.945 ↓ 393.4 19,670 1

Nested Loop (cost=141,818.02..214,982.83 rows=50 width=1,076) (actual time=3,657.670..8,250.945 rows=19,670 loops=1)

  • Join Filter: ((mix_orders.id = orders.id) AND (((mix_orders.processed_at)::date + (orders.processed_at - ((orders.processed_at)::date)::timestamp with time zone)) <= now()))
  • Rows Removed by Join Filter: 21975
8. 351.491 7,740.722 ↓ 1.4 54,358 1

Hash Join (cost=141,817.60..152,013.62 rows=39,894 width=20) (actual time=3,657.533..7,740.722 rows=54,358 loops=1)

  • Hash Cond: (mix_orders.id = order_routes.order_id)
9. 6,061.955 6,061.955 ↓ 1.4 54,358 1

CTE Scan on mix_orders (cost=0.00..797.88 rows=39,894 width=12) (actual time=2,330.127..6,061.955 rows=54,358 loops=1)

10. 514.617 1,327.276 ↓ 1.0 2,148,117 1

Hash (cost=106,917.82..106,917.82 rows=2,127,182 width=8) (actual time=1,327.276..1,327.276 rows=2,148,117 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 20515kB
11. 812.659 812.659 ↓ 1.0 2,148,117 1

Seq Scan on order_routes (cost=0.00..106,917.82 rows=2,127,182 width=8) (actual time=0.005..812.659 rows=2,148,117 loops=1)

12. 380.506 380.506 ↑ 1.0 1 54,358

Index Scan using orders_pkey on orders (cost=0.43..1.55 rows=1 width=1,068) (actual time=0.007..0.007 rows=1 loops=54,358)

  • Index Cond: (id = order_routes.order_id)
  • Filter: ((COALESCE(order_routes.source_city_id, city_id) = 50) AND (status = ANY ('{pre-order,purchasing,assigning,ordered,cancel,deliverying,done,return}'::order_status_enum[])))
  • Rows Removed by Filter: 0
Planning time : 1.901 ms
Execution time : 8,285.912 ms