explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sDPZ

Settings
# exclusive inclusive rows x rows loops node
1. 65.759 488.299 ↓ 3.3 18,102 1

HashAggregate (cost=18,335.77..18,390.04 rows=5,427 width=220) (actual time=472.460..488.299 rows=18,102 loops=1)

  • Group Key: location_summary.id, location_summary.soldbylocid, location_summary.soldtolocid, location_summary.shiptolocid
2.          

CTE location_summary

3. 43.430 375.886 ↑ 1.1 48,686 1

Unique (cost=14,401.19..15,893.62 rows=54,270 width=220) (actual time=308.639..375.886 rows=48,686 loops=1)

4. 83.480 332.456 ↑ 1.1 51,562 1

Sort (cost=14,401.19..14,536.87 rows=54,270 width=220) (actual time=308.637..332.456 rows=51,562 loops=1)

  • Sort Key: device.id, document_control.soldbylocid, document_control.soldtolocid, document_control.shiptolocid, geo_location.gps_lat, geo_location.gps_l
  • Sort Method: external merge Disk: 3280kB
5. 37.013 248.976 ↑ 1.1 51,562 1

Append (cost=1,026.70..6,765.28 rows=54,270 width=220) (actual time=52.250..248.976 rows=51,562 loops=1)

6. 18.251 91.856 ↓ 1.0 18,102 1

Hash Join (cost=1,026.70..2,074.20 rows=18,090 width=173) (actual time=52.250..91.856 rows=18,102 loops=1)

  • Hash Cond: (device.doc_control_id = document_control.id)
7. 21.448 21.448 ↓ 1.0 18,102 1

Seq Scan on device (cost=0.00..798.90 rows=18,090 width=12) (actual time=0.020..21.448 rows=18,102 loops=1)

8. 5.096 52.157 ↓ 1.0 9,286 1

Hash (cost=910.79..910.79 rows=9,273 width=45) (actual time=52.157..52.157 rows=9,286 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 835kB
9. 9.181 47.061 ↓ 1.0 9,286 1

Hash Join (cost=528.56..910.79 rows=9,273 width=45) (actual time=23.432..47.061 rows=9,286 loops=1)

  • Hash Cond: (document_control.soldbylocid = geo_location.id)
10. 14.523 14.523 ↓ 1.0 9,286 1

Seq Scan on document_control (cost=0.00..254.73 rows=9,273 width=28) (actual time=0.002..14.523 rows=9,286 loops=1)

11. 5.546 23.357 ↑ 1.0 10,012 1

Hash (cost=400.47..400.47 rows=10,247 width=21) (actual time=23.357..23.357 rows=10,012 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 666kB
12. 17.811 17.811 ↑ 1.0 10,012 1

Seq Scan on geo_location (cost=0.00..400.47 rows=10,247 width=21) (actual time=0.003..17.811 rows=10,012 loops=1)

13. 18.549 54.269 ↓ 1.0 18,102 1

Hash Join (cost=1,026.69..2,074.19 rows=18,090 width=173) (actual time=28.040..54.269 rows=18,102 loops=1)

  • Hash Cond: (device_1.doc_control_id = document_control_1.id)
14. 7.725 7.725 ↓ 1.0 18,102 1

Seq Scan on device device_1 (cost=0.00..798.90 rows=18,090 width=12) (actual time=0.004..7.725 rows=18,102 loops=1)

15. 4.951 27.995 ↓ 1.0 9,286 1

Hash (cost=910.78..910.78 rows=9,273 width=45) (actual time=27.995..27.995 rows=9,286 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 817kB
16. 13.056 23.044 ↓ 1.0 9,286 1

Hash Join (cost=528.56..910.78 rows=9,273 width=45) (actual time=10.062..23.044 rows=9,286 loops=1)

  • Hash Cond: (document_control_1.soldtolocid = geo_location_1.id)
  • -> Seq Scan on document_control document_control_1 (cost=0.00..254.73 rows=9273 width=28) (actual time=0.002..3.869 rows=9286
17. 9.988 9.988 ↑ 1.0 10,012 1

Hash (cost=400.47..400.47 rows=10,247 width=21) (actual time=9.988..9.988 rows=10,012 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 666kB
  • -> Seq Scan on geo_location geo_location_1 (cost=0.00..400.47 rows=10247 width=21) (actual time=0.005..5.062 rows=10012
18. 12.354 65.838 ↑ 1.2 15,358 1

Subquery Scan on *SELECT* 3 (cost=1,026.69..2,255.09 rows=18,090 width=220) (actual time=28.251..65.838 rows=15,358 loops=1)

19. 17.695 53.484 ↑ 1.2 15,358 1

Hash Join (cost=1,026.69..2,074.19 rows=18,090 width=173) (actual time=28.247..53.484 rows=15,358 loops=1)

  • Hash Cond: (device_2.doc_control_id = document_control_2.id)
20. 7.629 7.629 ↓ 1.0 18,102 1

Seq Scan on device device_2 (cost=0.00..798.90 rows=18,090 width=12) (actual time=0.004..7.629 rows=18,102 loops=1)

21. 4.934 28.160 ↓ 1.0 9,285 1

Hash (cost=910.78..910.78 rows=9,273 width=45) (actual time=28.160..28.160 rows=9,285 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 816kB
22. 13.170 23.226 ↓ 1.0 9,285 1

Hash Join (cost=528.56..910.78 rows=9,273 width=45) (actual time=10.130..23.226 rows=9,285 loops=1)

  • Hash Cond: (document_control_2.shiptolocid = geo_location_2.id)
  • -> Seq Scan on document_control document_control_2 (cost=0.00..254.73 rows=9273 width=28) (actual time=0.002..3.814 row
23. 10.056 10.056 ↑ 1.0 10,012 1

Hash (cost=400.47..400.47 rows=10,247 width=21) (actual time=10.055..10.056 rows=10,012 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 666kB
  • -> Seq Scan on geo_location geo_location_2 (cost=0.00..400.47 rows=10247 width=21) (actual time=0.004..5.098 rows
24. 422.540 422.540 ↑ 1.1 48,686 1

CTE Scan on location_summary (cost=0.00..1,085.40 rows=54,270 width=220) (actual time=308.642..422.540 rows=48,686 loops=1)