explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KUhZ

Settings
# exclusive inclusive rows x rows loops node
1. 999.714 3,104,585.647 ↓ 125,702.3 879,916 1

Nested Loop Left Join (cost=560,306.45..628,340.69 rows=7 width=68) (actual time=347,756.447..3,104,585.647 rows=879,916 loops=1)

2.          

Initplan (forNested Loop Left Join)

3. 0.023 0.087 ↑ 1.0 1 1

Nested Loop Semi Join (cost=0.28..5.42 rows=1 width=32) (actual time=0.085..0.087 rows=1 loops=1)

4. 0.040 0.040 ↓ 6.0 6 1

Seq Scan on tbl_road_endpoint_attributes (cost=0.00..1.11 rows=1 width=36) (actual time=0.036..0.040 rows=6 loops=1)

  • Filter: (tier = '{"statistic": "is_valid_start"}'::jsonb)
  • Rows Removed by Filter: 5
5. 0.024 0.024 ↓ 0.0 0 6

Index Only Scan using pk_datasetlinks on tbl_dataset_links (cost=0.28..4.30 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=6)

  • Index Cond: ((parent_dataset_id = 223) AND (child_dataset_id = tbl_road_endpoint_attributes.dataset_id))
  • Heap Fetches: 1
6. 0.004 0.076 ↑ 1.0 1 1

Nested Loop Semi Join (cost=0.28..5.42 rows=1 width=4) (actual time=0.075..0.076 rows=1 loops=1)

7. 0.042 0.042 ↓ 6.0 6 1

Seq Scan on tbl_road_endpoint_attributes tbl_road_endpoint_attributes_1 (cost=0.00..1.11 rows=1 width=8) (actual time=0.034..0.042 rows=6 loops=1)

  • Filter: (tier = '{"statistic": "is_valid_start"}'::jsonb)
  • Rows Removed by Filter: 5
8. 0.030 0.030 ↓ 0.0 0 6

Index Only Scan using pk_datasetlinks on tbl_dataset_links tbl_dataset_links_1 (cost=0.28..4.30 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=6)

  • Index Cond: ((parent_dataset_id = 223) AND (child_dataset_id = tbl_road_endpoint_attributes_1.dataset_id))
  • Heap Fetches: 1
9. 0.320 0.394 ↑ 1.0 1 1

Index Scan using tbl_zones_layer_id_idx on tbl_zones (cost=5.90..1,105.64 rows=1 width=32) (actual time=0.206..0.394 rows=1 loops=1)

  • Index Cond: (layer_id = 1)
  • Filter: ((name)::text = $5)
  • Rows Removed by Filter: 257
10.          

Initplan (forIndex Scan)

11. 0.002 0.074 ↑ 1.0 1 1

Limit (cost=0.57..5.48 rows=1 width=32) (actual time=0.073..0.074 rows=1 loops=1)

12. 0.033 0.072 ↑ 1,609,658.0 1 1

Nested Loop (cost=0.57..7,902,795.52 rows=1,609,658 width=32) (actual time=0.072..0.072 rows=1 loops=1)

13. 0.010 0.010 ↑ 1,609,658.0 1 1

Seq Scan on spatialstructurebuilder_v2_c83b23dcb9__sub s (cost=0.00..45,820.58 rows=1,609,658 width=4) (actual time=0.010..0.010 rows=1 loops=1)

14. 0.029 0.029 ↑ 1.0 1 1

Index Scan using tbl_roads_id_idx on tbl_roads r_1 (cost=0.57..4.87 rows=1 width=7) (actual time=0.029..0.029 rows=1 loops=1)

  • Index Cond: (id = s.road_id)
15. 1,180.883 2,337,178.540 ↓ 125,702.3 879,916 1

Nested Loop (cost=559,189.41..627,169.48 rows=7 width=4) (actual time=347,745.230..2,337,178.540 rows=879,916 loops=1)

16. 3,830.163 348,756.107 ↓ 154.9 1,227,450 1

HashAggregate (cost=559,188.84..559,268.07 rows=7,923 width=4) (actual time=347,710.596..348,756.107 rows=1,227,450 loops=1)

  • Group Key: re.id
17. 980.126 344,925.944 ↑ 1.3 3,215,108 1

Nested Loop (cost=402.60..548,561.07 rows=4,251,106 width=4) (actual time=77,275.205..344,925.944 rows=3,215,108 loops=1)

18. 139,522.413 175,923.838 ↓ 303.0 2,400,314 1

Bitmap Heap Scan on tbl_road_endpoints re (cost=402.18..43,542.61 rows=7,923 width=36) (actual time=77,274.897..175,923.838 rows=2,400,314 loops=1)

  • Recheck Cond: (geo_location && $6)
  • Filter: (dataset_id = 223)
  • Rows Removed by Filter: 4225805
  • Heap Blocks: exact=1047142
19. 36,401.425 36,401.425 ↓ 566.1 6,626,119 1

Bitmap Index Scan on tbl_road_endpoints_geo_location_idx (cost=0.00..400.20 rows=11,704 width=0) (actual time=36,401.425..36,401.425 rows=6,626,119 loops=1)

  • Index Cond: (geo_location && $6)
20. 168,021.980 168,021.980 ↑ 54.0 1 2,400,314

Index Scan using spatialstructurebuilder_v2_c83b23dcb9__sub_geo_linestring_idx on spatialstructurebuilder_v2_c83b23dcb9__sub r (cost=0.41..63.20 rows=54 width=32) (actual time=0.046..0.070 rows=1 loops=2,400,314)

  • Index Cond: (geo_linestring && re.geo_location)
  • Filter: _st_intersects(geo_linestring, re.geo_location)
  • Rows Removed by Filter: 0
21. 1,987,241.550 1,987,241.550 ↑ 1.0 1 1,227,450

Index Scan using pk_roadendpointattributevalues on tbl_road_endpoint_attribute_values eav (cost=0.57..8.56 rows=1 width=4) (actual time=1.615..1.619 rows=1 loops=1,227,450)

  • Index Cond: ((road_endpoint_id = re.id) AND (road_endpoint_attribute_id = $3))
  • Filter: (lower((value_text)::text) = 'true'::text)
  • Rows Removed by Filter: 0
22. 766,406.836 766,406.836 ↑ 1.0 1 879,916

Index Scan using tbl_road_endpoints_id_idx on tbl_road_endpoints e (cost=0.57..7.81 rows=1 width=36) (actual time=0.869..0.871 rows=1 loops=879,916)

  • Index Cond: (id = eav.road_endpoint_id)