explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lxQs

Settings
# exclusive inclusive rows x rows loops node
1. 0.833 1,415.206 ↑ 581.3 18 1

HashAggregate (cost=552,936.75..553,067.54 rows=10,463 width=100) (actual time=1,415.160..1,415.206 rows=18 loops=1)

  • Group Key: mrd.id, (sum(CASE WHEN ((((mrdd.engine_hours + mrdd.duty_hours) + mrdd.duty_hours_driver) = '0'::double precision) OR (((mrd.engine_hours + mrd.duty_hours) + mrd.duty_hours_driver) = '0'::double precision)) THEN '0'::numeric ELSE ceil(((((mrdd.engine_hours + mrdd.duty_hours) + mrdd.duty_hours_driver) / ((mrd.engine_hours + mrd.duty_hours) + mrd.duty_hours_driver)))::numeric) END))
  • Functions: 48
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 6.051 ms, Inlining 10.982 ms, Optimization 591.604 ms, Emission 317.970 ms, Total 926.607 ms
2. 97.225 1,414.373 ↑ 196.3 533 1

Merge Left Join (cost=25,161.77..549,797.79 rows=104,632 width=68) (actual time=1,399.076..1,414.373 rows=533 loops=1)

  • Merge Cond: (mrd.id = w.month_request_data_id)
  • Join Filter: (date_part('month'::text, w.target_date) = (mr.month)::double precision)
3. 0.412 937.416 ↑ 5,812.9 18 1

GroupAggregate (cost=25,161.34..30,916.10 rows=104,632 width=64) (actual time=937.016..937.416 rows=18 loops=1)

  • Group Key: mrd.id, mr.month
4. 0.155 937.004 ↑ 199.3 525 1

Sort (cost=25,161.34..25,422.92 rows=104,632 width=56) (actual time=936.957..937.004 rows=525 loops=1)

  • Sort Key: mrd.id, mr.month
  • Sort Method: quicksort Memory: 98kB
5. 0.058 936.849 ↑ 199.3 525 1

Nested Loop (cost=23.85..16,437.67 rows=104,632 width=56) (actual time=929.877..936.849 rows=525 loops=1)

6. 0.285 936.631 ↑ 364.6 20 1

Hash Join (cost=23.42..3,125.85 rows=7,293 width=32) (actual time=929.844..936.631 rows=20 loops=1)

  • Hash Cond: (mrd.agreement_id = a.id)
7. 6.301 936.279 ↑ 3.8 4,000 1

Hash Join (cost=2.78..3,064.63 rows=15,322 width=36) (actual time=924.439..936.279 rows=4,000 loops=1)

  • Hash Cond: (mrd.month_request_id = mr.id)
8. 929.942 929.942 ↓ 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=922.716..929.942 rows=60,080 loops=1)

  • Filter: (deleted IS NULL)
  • Rows Removed by Filter: 4,057
9. 0.005 0.036 ↑ 3.1 8 1

Hash (cost=2.47..2.47 rows=25 width=8) (actual time=0.036..0.036 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
10. 0.031 0.031 ↑ 3.1 8 1

Seq Scan on month_request mr (cost=0.00..2.47 rows=25 width=8) (actual time=0.029..0.031 rows=8 loops=1)

  • Filter: ((deleted IS NULL) AND (date_start <= '2020-06-10'::date) AND (date_end > '2020-06-10'::date))
  • Rows Removed by Filter: 90
11. 0.003 0.067 ↑ 57.0 4 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
12. 0.064 0.064 ↑ 57.0 4 1

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

  • Filter: ((deleted IS NULL) AND (smart_service IS TRUE))
  • Rows Removed by Filter: 439
13. 0.160 0.160 ↓ 1.1 26 20

Index Scan using month_request_data_day_877358ce on month_request_data_day mrdd (cost=0.42..1.59 rows=24 width=28) (actual time=0.004..0.008 rows=26 loops=20)

  • Index Cond: (month_request_data_id = mrd.id)
  • Filter: (deleted IS NULL)
14. 192.302 379.732 ↑ 4.4 641,999 1

Materialize (cost=0.43..391,217.83 rows=2,825,286 width=20) (actual time=0.017..379.732 rows=641,999 loops=1)

15. 187.430 187.430 ↑ 4.4 641,999 1

Index Scan using waybill_877358ce on waybill w (cost=0.43..384,154.62 rows=2,825,286 width=20) (actual time=0.014..187.430 rows=641,999 loops=1)

Planning time : 1.004 ms
Execution time : 1,421.747 ms