explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hD3q : new

Settings
# exclusive inclusive rows x rows loops node
1. 2,594.287 50,766.179 ↓ 37.1 5,120,709 1

GroupAggregate (cost=2,284,191.48..2,306,634.81 rows=138,113 width=60) (actual time=47,231.008..50,766.179 rows=5,120,709 loops=1)

  • Group Key: a.application_user_id, a.day, a.hour, a.address_id
2. 3,288.905 48,171.892 ↓ 4.0 5,542,566 1

Sort (cost=2,284,191.48..2,287,644.30 rows=1,381,128 width=36) (actual time=47,230.996..48,171.892 rows=5,542,566 loops=1)

  • Sort Key: a.application_user_id, a.day, a.hour, a.address_id
  • Sort Method: external merge Disk: 256136kB
3. 719.073 44,882.987 ↓ 4.0 5,542,566 1

Subquery Scan on a (cost=2,103,948.62..2,121,212.72 rows=1,381,128 width=36) (actual time=43,210.022..44,882.987 rows=5,542,566 loops=1)

4. 3,814.289 44,163.914 ↓ 4.0 5,542,566 1

Sort (cost=2,103,948.62..2,107,401.44 rows=1,381,128 width=52) (actual time=43,210.020..44,163.914 rows=5,542,566 loops=1)

  • Sort Key: orders.application_user_id, (date_part('isodow'::text, orders.created_at)), (date_part('hour'::text, orders.created_at)), (sum(CASE WHEN (s.corridor_super_store_id IS NULL) THEN 0 ELSE 1 END)) DESC, (count(s.store_id)) DESC
  • Sort Method: external merge Disk: 357432kB
5. 2,158.283 40,349.625 ↓ 4.0 5,542,566 1

GroupAggregate (cost=1,887,101.53..1,935,441.01 rows=1,381,128 width=52) (actual time=35,446.337..40,349.625 rows=5,542,566 loops=1)

  • Group Key: orders.application_user_id, (date_part('isodow'::text, orders.created_at)), (date_part('hour'::text, orders.created_at)), s.store_id, orders.address_id
6. 6,259.221 38,191.342 ↓ 4.2 5,792,809 1

Sort (cost=1,887,101.53..1,890,554.35 rows=1,381,128 width=36) (actual time=35,446.331..38,191.342 rows=5,792,809 loops=1)

  • Sort Key: orders.application_user_id, (date_part('isodow'::text, orders.created_at)), (date_part('hour'::text, orders.created_at)), s.store_id, orders.address_id
  • Sort Method: external merge Disk: 267744kB
7. 7,422.947 31,932.121 ↓ 4.2 5,792,809 1

Hash Join (cost=926,999.15..1,724,122.77 rows=1,381,128 width=36) (actual time=16,575.032..31,932.121 rows=5,792,809 loops=1)

  • Hash Cond: (orders.id = calculated_orders.order_id)
8. 8,014.611 8,014.611 ↓ 1.0 7,190,276 1

Seq Scan on orders (cost=0.00..633,522.50 rows=7,142,432 width=24) (actual time=0.014..8,014.611 rows=7,190,276 loops=1)

  • Filter: (((state)::text = ANY ('{finished,pending_review}'::text[])) AND (created_at >= '2019-01-07 09:16:37.444234'::timestamp without time zone))
  • Rows Removed by Filter: 8211804
9. 2,533.451 16,494.563 ↓ 3.7 11,056,854 1

Hash (cost=875,230.88..875,230.88 rows=2,978,102 width=12) (actual time=16,494.563..16,494.563 rows=11,056,854 loops=1)

  • Buckets: 2097152 (originally 2097152) Batches: 16 (originally 4) Memory Usage: 49153kB
10. 3,098.788 13,961.112 ↓ 3.7 11,056,854 1

Hash Join (cost=8,739.49..875,230.88 rows=2,978,102 width=12) (actual time=14.015..13,961.112 rows=11,056,854 loops=1)

  • Hash Cond: (calculated_orders.store_id = s.store_id)
11. 10,848.366 10,848.366 ↓ 1.0 15,401,892 1

Seq Scan on orders calculated_orders (cost=0.00..779,022.18 rows=15,383,518 width=8) (actual time=0.015..10,848.366 rows=15,401,892 loops=1)

12. 1.372 13.958 ↓ 1.3 9,179 1

Hash (cost=8,649.01..8,649.01 rows=7,238 width=8) (actual time=13.957..13.958 rows=9,179 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 464kB
13. 12.586 12.586 ↓ 1.3 9,179 1

Seq Scan on stores s (cost=0.00..8,649.01 rows=7,238 width=8) (actual time=0.006..12.586 rows=9,179 loops=1)

  • Filter: ((deleted_at IS NULL) AND is_enabled)
  • Rows Removed by Filter: 26059