explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GfWd

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 115.373 ↑ 1.0 1 1

Limit (cost=145,447.24..145,447.27 rows=1 width=720) (actual time=115.362..115.373 rows=1 loops=1)

2.          

CTE _cen

3. 0.054 0.119 ↑ 23,100.0 1 1

Hash Left Join (cost=19.11..496.11 rows=23,100 width=119) (actual time=0.117..0.119 rows=1 loops=1)

  • Hash Cond: ((t_centres.cen_pcentres)::text = (ggc.ggc_fcentres)::text)
4. 0.010 0.057 ↑ 210.0 1 1

Nested Loop Left Join (cost=4.16..10.55 rows=210 width=154) (actual time=0.056..0.057 rows=1 loops=1)

5. 0.026 0.026 ↑ 1.0 1 1

Seq Scan on t_centres (cost=0.00..1.02 rows=1 width=146) (actual time=0.025..0.026 rows=1 loops=1)

  • Filter: (cen_icao = 'LEMD'::bpchar)
  • Rows Removed by Filter: 1
6. 0.011 0.021 ↑ 2.0 1 1

Bitmap Heap Scan on a_pois_versions apv (cost=4.16..9.50 rows=2 width=154) (actual time=0.020..0.021 rows=1 loops=1)

  • Recheck Cond: ((apv_fcentres)::text = (t_centres.cen_pcentres)::text)
  • Heap Blocks: exact=1
7. 0.010 0.010 ↑ 2.0 1 1

Bitmap Index Scan on a_pois_versions_apv_fcentres_idx (cost=0.00..4.16 rows=2 width=0) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: ((apv_fcentres)::text = (t_centres.cen_pcentres)::text)
8. 0.003 0.008 ↑ 220.0 1 1

Hash (cost=12.20..12.20 rows=220 width=154) (actual time=0.008..0.008 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 0.005 0.005 ↑ 220.0 1 1

Seq Scan on g_graph_configs ggc (cost=0.00..12.20 rows=220 width=154) (actual time=0.005..0.005 rows=1 loops=1)

10. 0.008 115.360 ↑ 23,100.0 1 1

Nested Loop (cost=144,951.13..145,644.15 rows=23,100 width=720) (actual time=115.360..115.360 rows=1 loops=1)

11. 14.023 115.350 ↑ 1.0 1 1

Aggregate (cost=144,951.13..144,951.14 rows=1 width=32) (actual time=115.350..115.350 rows=1 loops=1)

12. 0.796 101.327 ↑ 151.6 8 1

Subquery Scan on q2 (cost=140,080.96..144,948.09 rows=1,213 width=226) (actual time=4.578..101.327 rows=8 loops=1)

13. 98.495 100.531 ↑ 151.6 8 1

GroupAggregate (cost=140,080.96..144,935.96 rows=1,213 width=362) (actual time=4.525..100.531 rows=8 loops=1)

  • Group Key: q3.bui_group_id, q3.bui_category, q3.bui_fcentres, q3.bui_situm_id, q3.centroid, q3.bui_name, q3.bui_alt_name, q3.bui_color, q3.bui_priority
14. 0.255 2.036 ↑ 121.3 40 1

Sort (cost=140,080.96..140,093.09 rows=4,851 width=33,398) (actual time=2.022..2.036 rows=40 loops=1)

  • Sort Key: q3.bui_group_id, q3.bui_category, q3.bui_fcentres, q3.bui_situm_id, q3.centroid, q3.bui_name, q3.bui_alt_name, q3.bui_color, q3.bui_priority
  • Sort Method: quicksort Memory: 117kB
15. 0.012 1.781 ↑ 121.3 40 1

Subquery Scan on q3 (cost=70,193.17..70,508.48 rows=4,851 width=33,398) (actual time=1.051..1.781 rows=40 loops=1)

16. 0.921 1.769 ↑ 121.3 40 1

WindowAgg (cost=70,193.17..70,459.97 rows=4,851 width=33,408) (actual time=1.049..1.769 rows=40 loops=1)

17. 0.110 0.848 ↑ 121.3 40 1

Sort (cost=70,193.17..70,205.30 rows=4,851 width=33,262) (actual time=0.843..0.848 rows=40 loops=1)

  • Sort Key: t_buildings.bui_group_id, t_buildings.bui_priority DESC
  • Sort Method: quicksort Memory: 114kB
18. 0.034 0.738 ↑ 121.3 40 1

Hash Left Join (cost=777.11..904.19 rows=4,851 width=33,262) (actual time=0.695..0.738 rows=40 loops=1)

  • Hash Cond: ((t_buildings.bui_pbuildings)::text = (tl.lev_fbuildings)::text)
19. 0.189 0.485 ↑ 126.0 11 1

Hash Join (cost=756.16..819.14 rows=1,386 width=33,235) (actual time=0.455..0.485 rows=11 loops=1)

  • Hash Cond: ((t_buildings.bui_fcentres)::text = (_cen_1.cen_pcentres)::text)
20. 0.035 0.170 ↑ 1.0 12 1

Hash Right Join (cost=5.41..19.85 rows=12 width=33,235) (actual time=0.144..0.170 rows=12 loops=1)

  • Hash Cond: ((t_geofences.gfe_fbuildings)::text = (t_buildings.bui_pbuildings)::text)
21. 0.063 0.063 ↑ 1.0 12 1

Index Scan using t_geofences_gfe_category_idx on t_geofences (cost=0.14..14.54 rows=12 width=24,627) (actual time=0.050..0.063 rows=12 loops=1)

  • Index Cond: ((gfe_category)::text = 'BUILDING_FENCE'::text)
22. 0.032 0.072 ↑ 1.0 12 1

Hash (cost=5.12..5.12 rows=12 width=8,625) (actual time=0.071..0.072 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
23. 0.040 0.040 ↑ 1.0 12 1

Seq Scan on t_buildings (cost=0.00..5.12 rows=12 width=8,625) (actual time=0.012..0.040 rows=12 loops=1)

24. 0.004 0.126 ↑ 23,100.0 1 1

Hash (cost=462.00..462.00 rows=23,100 width=146) (actual time=0.126..0.126 rows=1 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 257kB
25. 0.122 0.122 ↑ 23,100.0 1 1

CTE Scan on _cen _cen_1 (cost=0.00..462.00 rows=23,100 width=146) (actual time=0.120..0.122 rows=1 loops=1)

26. 0.025 0.219 ↑ 1.0 42 1

Hash (cost=20.42..20.42 rows=42 width=61) (actual time=0.218..0.219 rows=42 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
27. 0.194 0.194 ↑ 1.0 42 1

Seq Scan on t_levels tl (cost=0.00..20.42 rows=42 width=61) (actual time=0.007..0.194 rows=42 loops=1)

28. 0.002 0.002 ↑ 23,100.0 1 1

CTE Scan on _cen (cost=0.00..462.00 rows=23,100 width=688) (actual time=0.001..0.002 rows=1 loops=1)

Planning time : 3.487 ms
Execution time : 136.989 ms