explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LLjX

Settings
# exclusive inclusive rows x rows loops node
1. 102.430 9,257.382 ↓ 6.0 238,515 1

Unique (cost=2,635,485.84..2,735,316.66 rows=40,000 width=20) (actual time=8,994.332..9,257.382 rows=238,515 loops=1)

2. 725.942 9,154.952 ↑ 10.7 1,244,700 1

Sort (cost=2,635,485.84..2,668,762.78 rows=13,310,776 width=20) (actual time=8,994.331..9,154.952 rows=1,244,700 loops=1)

  • Sort Key: "*SELECT* 1".zone_id, "*SELECT* 1".road_id, dd.cost
  • Sort Method: quicksort Memory: 146392kB
3. 403.526 8,429.010 ↑ 10.7 1,244,700 1

Hash Right Join (cost=7,854.89..1,060,415.64 rows=13,310,776 width=20) (actual time=54.430..8,429.010 rows=1,244,700 loops=1)

  • Hash Cond: ("*SELECT* 1".source_point_id = dd.id1)
4. 68.135 7,997.072 ↑ 10.7 1,244,613 1

Append (cost=3,927.45..873,465.02 rows=13,310,776 width=16) (actual time=25.761..7,997.072 rows=1,244,613 loops=1)

5. 53.181 3,989.306 ↑ 10.7 622,820 1

Subquery Scan on *SELECT* 1 (cost=3,927.45..436,732.51 rows=6,655,388 width=16) (actual time=25.761..3,989.306 rows=622,820 loops=1)

6. 1,328.955 3,936.125 ↑ 10.7 622,820 1

Hash Semi Join (cost=3,927.45..370,178.63 rows=6,655,388 width=16) (actual time=25.760..3,936.125 rows=622,820 loops=1)

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

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

8. 0.002 0.002 ↓ 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.002..0.002 rows=0 loops=1)

  • Filter: (spatial_structure_id = ANY ('{6}'::integer[]))
9. 1,872.178 1,872.178 ↑ 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.006..1,872.178 rows=13,307,421 loops=1)

  • Filter: (spatial_structure_id = ANY ('{6}'::integer[]))
10. 13.851 25.591 ↑ 1.0 140,731 1

Hash (cost=2,168.31..2,168.31 rows=140,731 width=4) (actual time=25.591..25.591 rows=140,731 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 6996kB
11. 11.740 11.740 ↑ 1.0 140,731 1

Seq Scan on dd dd_1 (cost=0.00..2,168.31 rows=140,731 width=4) (actual time=0.004..11.740 rows=140,731 loops=1)

12. 54.299 3,939.631 ↑ 10.7 621,793 1

Subquery Scan on *SELECT* 2 (cost=3,927.45..436,732.51 rows=6,655,388 width=16) (actual time=32.947..3,939.631 rows=621,793 loops=1)

13. 1,271.398 3,885.332 ↑ 10.7 621,793 1

Hash Semi Join (cost=3,927.45..370,178.63 rows=6,655,388 width=16) (actual time=32.945..3,885.332 rows=621,793 loops=1)

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

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

15. 0.004 0.004 ↓ 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.004..0.004 rows=0 loops=1)

  • Filter: (spatial_structure_id = ANY ('{6}'::integer[]))
16. 1,872.318 1,872.318 ↑ 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,872.318 rows=13,307,421 loops=1)

  • Filter: (spatial_structure_id = ANY ('{6}'::integer[]))
17. 17.389 32.774 ↑ 1.0 140,731 1

Hash (cost=2,168.31..2,168.31 rows=140,731 width=4) (actual time=32.774..32.774 rows=140,731 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 6996kB
18. 15.385 15.385 ↑ 1.0 140,731 1

Seq Scan on dd dd_2 (cost=0.00..2,168.31 rows=140,731 width=4) (actual time=0.005..15.385 rows=140,731 loops=1)

19. 15.285 28.412 ↑ 1.0 140,731 1

Hash (cost=2,168.31..2,168.31 rows=140,731 width=12) (actual time=28.412..28.412 rows=140,731 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 8096kB
20. 13.127 13.127 ↑ 1.0 140,731 1

Seq Scan on dd (cost=0.00..2,168.31 rows=140,731 width=12) (actual time=0.019..13.127 rows=140,731 loops=1)

Planning time : 2.667 ms
Execution time : 9,272.453 ms