explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XuVU

Settings
# exclusive inclusive rows x rows loops node
1. 0.524 61,909.112 ↑ 1.0 31 1

Nested Loop (cost=1,322.83..70,962.18 rows=31 width=242) (actual time=4,020.904..61,909.112 rows=31 loops=1)

  • Buffers: shared hit=119,189
2. 0.279 31,430.256 ↑ 1.0 31 1

Hash Join (cost=1,322.56..3,441.24 rows=31 width=125) (actual time=2,857.187..31,430.256 rows=31 loops=1)

  • Buffers: shared hit=76,836
3. 0.208 1,518.034 ↑ 1.0 31 1

Hash Join (cost=104.36..2,150.87 rows=31 width=115) (actual time=60.963..1,518.034 rows=31 loops=1)

  • Buffers: shared hit=2,023
4. 0.199 0.199 ↑ 1.0 31 1

Seq Scan on epm_driver_sj esj (cost=0..37.67 rows=31 width=31) (actual time=0.021..0.199 rows=31 loops=1)

  • Filter: (esj.mfg_org_id = '121'::bigint)
  • Buffers: shared hit=17
5. 59.250 60.644 ↑ 1.0 2,016 1

Hash (cost=79.16..79.16 rows=2,016 width=84) (actual time=60.644..60.644 rows=2,016 loops=1)

  • Buffers: shared hit=59
6. 1.394 1.394 ↑ 1.0 2,016 1

Seq Scan on opr_sj_header sjh (cost=0..79.16 rows=2,016 width=84) (actual time=0.004..1.394 rows=2,016 loops=1)

  • Buffers: shared hit=59
7.          

SubPlan (for Hash Join)

8. 0.066 1,456.983 ↓ 0.0 0 33

Limit (cost=129.57..129.57 rows=1 width=24) (actual time=44.151..44.151 rows=0 loops=33)

  • Buffers: shared hit=1,947
9. 344.883 1,456.917 ↓ 0.0 0 33

Sort (cost=129.57..129.57 rows=1 width=24) (actual time=44.149..44.149 rows=0 loops=33)

  • Sort Key: sjh_1.depart_date DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1,947
10. 1,112.034 1,112.034 ↓ 0.0 0 33

Seq Scan on opr_sj_header sjh_1 (cost=0..129.56 rows=1 width=24) (actual time=30.639..33.698 rows=0 loops=33)

  • Filter: ((sjh_1.driver_id = esj.driver_person_id) AND ((sjh_1.status)::text = 'D'::text) AND (sjh_1.depart_date >= date_trunc('day'::text, (CURRENT_DATE - '4 days'::interval day))) AND (sjh_1.depart_date <= date_trunc('day'::text, (CURRENT_DATE + '1 day'::interval day))))
  • Buffers: shared hit=1,947
11. 1,251.335 2,366.579 ↓ 1.0 32,672 1

Hash (cost=810.31..810.31 rows=32,631 width=18) (actual time=2,366.578..2,366.579 rows=32,672 loops=1)

  • Buffers: shared hit=484
12. 1,115.244 1,115.244 ↓ 1.0 32,672 1

Seq Scan on opr_driver_sj_detail esd (cost=0..810.31 rows=32,631 width=18) (actual time=18.659..1,115.244 rows=32,672 loops=1)

  • Buffers: shared hit=484
13.          

SubPlan (for Hash Join)

14. 0.099 27,545.364 ↓ 0.0 0 33

Limit (cost=0.29..46.51 rows=1 width=8) (actual time=834.708..834.708 rows=0 loops=33)

  • Buffers: shared hit=74,329
15. 27,545.265 27,545.265 ↓ 0.0 0 33

Index Scan using opr_driver_sj_detail_pk on opr_driver_sj_detail esd_1 (cost=0.29..2,080.15 rows=45 width=8) (actual time=834.705..834.705 rows=0 loops=33)

  • Filter: ((esd_1.driver_id = esj.driver_person_id) AND (esd_1.create_date >= date_trunc('day'::text, (CURRENT_DATE - '2 days'::interval day))) AND (esd_1.create_date <= date_trunc('day'::text, (CURRENT_DATE + '1 day'::interval day))))
  • Buffers: shared hit=74,329
16. 0.217 0.217 ↓ 0.0 0 31

Index Scan using opr_loglocation_latest_driver_id_key on opr_loglocation_latest mml (cost=0.28..5.71 rows=1 width=35) (actual time=0.007..0.007 rows=0 loops=31)

  • Index Cond: (mml.driver_id = esj.driver_person_id)
  • Buffers: shared hit=69
17.          

SubPlan (for Nested Loop)

18. 529.108 15,444.696 ↑ 1.0 1 31

Aggregate (cost=1,086.18..1,086.19 rows=1 width=32) (actual time=498.216..498.216 rows=1 loops=31)

  • Buffers: shared hit=21,142
19. 14,915.588 14,915.588 ↑ 16.0 1 31

Seq Scan on opr_so_do a (cost=0..1,086.14 rows=16 width=10) (actual time=478.338..481.148 rows=1 loops=31)

  • Filter: ((a.sj_header_id)::text = (sjh.sj_header_id)::text)
  • Buffers: shared hit=21,142
20. 0.527 15,033.419 ↑ 1.0 1 31

Aggregate (cost=1,086.18..1,086.19 rows=1 width=32) (actual time=484.949..484.949 rows=1 loops=31)

  • Buffers: shared hit=21,142
21. 15,032.892 15,032.892 ↑ 16.0 1 31

Seq Scan on opr_so_do a_1 (cost=0..1,086.14 rows=16 width=9) (actual time=481.447..484.932 rows=1 loops=31)

  • Filter: ((a_1.sj_header_id)::text = (sjh.sj_header_id)::text)
  • Buffers: shared hit=21,142
Planning time : 308.169 ms
Execution time : 61,909.266 ms