explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y7iB

Settings
# exclusive inclusive rows x rows loops node
1. 197.055 11,774.041 ↓ 12.1 485,492 1

Unique (cost=2,649,350.60..2,749,181.42 rows=40,000 width=20) (actual time=11,251.212..11,774.041 rows=485,492 loops=1)

2. 1,533.998 11,576.986 ↑ 5.2 2,553,353 1

Sort (cost=2,649,350.60..2,682,627.54 rows=13,310,776 width=20) (actual time=11,251.211..11,576.986 rows=2,553,353 loops=1)

  • Sort Key: "*SELECT* 1".zone_id, "*SELECT* 1".road_id, dd.cost
  • Sort Method: quicksort Memory: 297167kB
3. 839.003 10,042.988 ↑ 5.2 2,553,353 1

Hash Right Join (cost=17,098.06..1,074,280.39 rows=13,310,776 width=20) (actual time=117.195..10,042.988 rows=2,553,353 loops=1)

  • Hash Cond: ("*SELECT* 1".source_point_id = dd.id1)
4. 142.509 9,144.353 ↑ 5.3 2,533,573 1

Append (cost=8,549.03..882,708.19 rows=13,310,776 width=16) (actual time=56.974..9,144.353 rows=2,533,573 loops=1)

5. 111.024 4,706.317 ↑ 5.3 1,266,594 1

Subquery Scan on *SELECT* 1 (cost=8,549.03..441,354.10 rows=6,655,388 width=16) (actual time=56.974..4,706.317 rows=1,266,594 loops=1)

6. 1,929.685 4,595.293 ↑ 5.3 1,266,594 1

Hash Semi Join (cost=8,549.03..374,800.22 rows=6,655,388 width=16) (actual time=56.973..4,595.293 rows=1,266,594 loops=1)

  • Hash Cond: (m.source_point_id = dd_1.id1)
7. 738.647 2,608.963 ↑ 1.0 13,307,421 1

Append (cost=0.00..257,269.21 rows=13,310,775 width=16) (actual time=0.012..2,608.963 rows=13,307,421 loops=1)

8. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on tbl_zone_road_mot_address_count_p m (cost=0.00..0.00 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=1)

  • Filter: (spatial_structure_id = ANY ('{6}'::integer[]))
9. 1,870.312 1,870.312 ↑ 1.0 13,307,421 1

Seq Scan on tbl_zone_road_mot_address_count_p_6 m_1 (cost=0.00..257,269.21 rows=13,310,774 width=16) (actual time=0.008..1,870.312 rows=13,307,421 loops=1)

  • Filter: (spatial_structure_id = ANY ('{6}'::integer[]))
10. 31.128 56.645 ↑ 1.0 306,357 1

Hash (cost=4,719.57..4,719.57 rows=306,357 width=4) (actual time=56.645..56.645 rows=306,357 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 14867kB
11. 25.517 25.517 ↑ 1.0 306,357 1

Seq Scan on dd dd_1 (cost=0.00..4,719.57 rows=306,357 width=4) (actual time=0.006..25.517 rows=306,357 loops=1)

12. 104.698 4,295.527 ↑ 5.3 1,266,979 1

Subquery Scan on *SELECT* 2 (cost=8,549.03..441,354.10 rows=6,655,388 width=16) (actual time=57.092..4,295.527 rows=1,266,979 loops=1)

13. 1,676.187 4,190.829 ↑ 5.3 1,266,979 1

Hash Semi Join (cost=8,549.03..374,800.22 rows=6,655,388 width=16) (actual time=57.092..4,190.829 rows=1,266,979 loops=1)

  • Hash Cond: (m_2.target_point_id = dd_2.id1)
14. 692.050 2,457.864 ↑ 1.0 13,307,421 1

Append (cost=0.00..257,269.21 rows=13,310,775 width=16) (actual time=0.012..2,457.864 rows=13,307,421 loops=1)

15. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on tbl_zone_road_mot_address_count_p m_2 (cost=0.00..0.00 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=1)

  • Filter: (spatial_structure_id = ANY ('{6}'::integer[]))
16. 1,765.809 1,765.809 ↑ 1.0 13,307,421 1

Seq Scan on tbl_zone_road_mot_address_count_p_6 m_3 (cost=0.00..257,269.21 rows=13,310,774 width=16) (actual time=0.007..1,765.809 rows=13,307,421 loops=1)

  • Filter: (spatial_structure_id = ANY ('{6}'::integer[]))
17. 31.221 56.778 ↑ 1.0 306,357 1

Hash (cost=4,719.57..4,719.57 rows=306,357 width=4) (actual time=56.778..56.778 rows=306,357 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 14867kB
18. 25.557 25.557 ↑ 1.0 306,357 1

Seq Scan on dd dd_2 (cost=0.00..4,719.57 rows=306,357 width=4) (actual time=0.006..25.557 rows=306,357 loops=1)

19. 31.926 59.632 ↑ 1.0 306,357 1

Hash (cost=4,719.57..4,719.57 rows=306,357 width=12) (actual time=59.632..59.632 rows=306,357 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 17260kB
20. 27.706 27.706 ↑ 1.0 306,357 1

Seq Scan on dd (cost=0.00..4,719.57 rows=306,357 width=12) (actual time=0.023..27.706 rows=306,357 loops=1)

Planning time : 3.307 ms
Execution time : 11,809.436 ms