explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5x0g

Settings
# exclusive inclusive rows x rows loops node
1. 11.581 2,682.862 ↑ 79.8 501 1

HashAggregate (cost=611,648.24..612,148.24 rows=40,000 width=68) (actual time=2,682.576..2,682.862 rows=501 loops=1)

  • Group Key: d.id, d.plan_shifts
  • Functions: 60
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 6.844 ms, Inlining 6.851 ms, Optimization 394.168 ms, Emission 241.154 ms, Total 649.017 ms
2. 41.337 2,671.281 ↑ 35.9 11,424 1

Merge Right Join (cost=562,536.09..599,343.47 rows=410,159 width=68) (actual time=2,549.978..2,671.281 rows=11,424 loops=1)

  • Merge Cond: ((w.month_request_data_id = d.id) AND ((date_part('month'::text, w.target_date)) = ((d.month)::double precision)))
3. 914.079 2,303.321 ↑ 3.1 777,598 1

Sort (cost=416,590.13..422,525.79 rows=2,374,264 width=20) (actual time=2,224.160..2,303.321 rows=777,598 loops=1)

  • Sort Key: w.month_request_data_id, (date_part('month'::text, w.target_date))
  • Sort Method: external merge Disk: 87,312kB
4. 1,389.242 1,389.242 ↓ 1.0 2,446,869 1

Seq Scan on waybill w (cost=0.00..116,474.86 rows=2,374,264 width=20) (actual time=643.245..1,389.242 rows=2,446,869 loops=1)

  • Filter: ((accepted IS TRUE) AND (deleted IS NULL))
  • Rows Removed by Filter: 376,429
5. 0.797 326.623 ↑ 35.9 11,412 1

Materialize (cost=145,945.96..147,996.75 rows=410,159 width=64) (actual time=325.783..326.623 rows=11,412 loops=1)

6. 0.162 325.826 ↑ 818.7 501 1

Sort (cost=145,945.96..146,971.36 rows=410,159 width=64) (actual time=325.779..325.826 rows=501 loops=1)

  • Sort Key: d.id, ((d.month)::double precision)
  • Sort Method: quicksort Memory: 95kB
7. 0.049 325.664 ↑ 818.7 501 1

Subquery Scan on d (cost=65,622.36..92,282.70 rows=410,159 width=64) (actual time=317.027..325.664 rows=501 loops=1)

8. 7.986 325.615 ↑ 818.7 501 1

GroupAggregate (cost=65,622.36..88,181.11 rows=410,159 width=64) (actual time=317.024..325.615 rows=501 loops=1)

  • Group Key: mrd.id, mr.month
9. 4.675 317.629 ↑ 34.5 11,888 1

Sort (cost=65,622.36..66,647.76 rows=410,159 width=56) (actual time=316.967..317.629 rows=11,888 loops=1)

  • Sort Key: mrd.id, mr.month
  • Sort Method: quicksort Memory: 2,056kB
10. 1.972 312.954 ↑ 34.5 11,888 1

Hash Left Join (cost=3,671.91..27,383.60 rows=410,159 width=56) (actual time=21.413..312.954 rows=11,888 loops=1)

  • Hash Cond: (mrd.month_request_id = mr.id)
11. 62.482 310.947 ↑ 34.5 11,888 1

Hash Join (cost=3,668.78..26,257.24 rows=410,159 width=56) (actual time=21.364..310.947 rows=11,888 loops=1)

  • Hash Cond: (mrd.agreement_id = a.id)
12. 142.598 248.385 ↓ 1.1 914,547 1

Hash Join (cost=3,648.14..23,954.64 rows=861,694 width=60) (actual time=20.965..248.385 rows=914,547 loops=1)

  • Hash Cond: (mrdd.month_request_data_id = mrd.id)
13. 85.198 85.198 ↓ 1.0 920,955 1

Seq Scan on month_request_data_day mrdd (cost=0.00..17,890.93 rows=920,157 width=28) (actual time=0.149..85.198 rows=920,955 loops=1)

  • Filter: (deleted IS NULL)
  • Rows Removed by Filter: 49,538
14. 9.542 20.589 ↓ 1.0 60,080 1

Hash (cost=2,897.37..2,897.37 rows=60,062 width=36) (actual time=20.588..20.589 rows=60,080 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 4,737kB
15. 11.047 11.047 ↓ 1.0 60,080 1

Seq Scan on month_request_data mrd (cost=0.00..2,897.37 rows=60,062 width=36) (actual time=0.011..11.047 rows=60,080 loops=1)

  • Filter: (deleted IS NULL)
  • Rows Removed by Filter: 4,057
16. 0.004 0.080 ↑ 57.0 4 1

Hash (cost=17.79..17.79 rows=228 width=4) (actual time=0.080..0.080 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 0.076 0.076 ↑ 57.0 4 1

Seq Scan on agreement a (cost=0.00..17.79 rows=228 width=4) (actual time=0.073..0.076 rows=4 loops=1)

  • Filter: ((deleted IS NULL) AND (smart_service IS TRUE))
  • Rows Removed by Filter: 439
18. 0.015 0.035 ↑ 1.0 92 1

Hash (cost=1.98..1.98 rows=92 width=8) (actual time=0.035..0.035 rows=92 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
19. 0.020 0.020 ↑ 1.0 92 1

Seq Scan on month_request mr (cost=0.00..1.98 rows=92 width=8) (actual time=0.014..0.020 rows=92 loops=1)

  • Filter: (deleted IS NULL)
  • Rows Removed by Filter: 6
Planning time : 1.858 ms
Execution time : 2,705.955 ms