explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8acY : Optimization for: Optimized new poiints in area code; plan #QJMP

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 267.073 267.073 ↓ 26.1 626 1

CTE Scan on get_points (cost=6,844.72..6,845.20 rows=24 width=232) (actual time=76.296..267.073 rows=626 loops=1)

2.          

CTE selected_points

3. 25.968 25.968 ↓ 13.1 631 1

Seq Scan on points (cost=0.00..2,835.62 rows=48 width=362) (actual time=0.116..25.968 rows=631 loops=1)

  • Filter: ('01030000000100000005000000295C8FC2F56822401F85EB51B86E4840295C8FC2F5682240AE47E17A146E48406666666666662240AE47E17A146E484066666666666622401F85EB51B86E4840295C8FC2F56822401F85EB51B86E4840'::geometry ~ offset_geom)
  • Rows Removed by Filter: 46939
4.          

CTE filtered_points

5. 26.524 26.524 ↓ 26.1 626 1

CTE Scan on selected_points points_1 (cost=0.00..0.96 rows=24 width=951) (actual time=0.119..26.524 rows=626 loops=1)

  • Filter: ((offset_geom IS NOT NULL) AND (is_duplicate IS FALSE))
  • Rows Removed by Filter: 5
6.          

CTE objects

7. 17.351 30.238 ↑ 1.1 6,943 1

Hash Join (cost=258.38..3,404.92 rows=7,306 width=47) (actual time=4.360..30.238 rows=6,943 loops=1)

  • Hash Cond: ((points_2.id)::text = detected_objects.point_id)
8. 8.554 8.554 ↑ 1.0 47,570 1

Seq Scan on points points_2 (cost=0.00..2,716.70 rows=47,570 width=37) (actual time=0.003..8.554 rows=47,570 loops=1)

9. 2.716 4.333 ↑ 1.0 7,306 1

Hash (cost=167.06..167.06 rows=7,306 width=47) (actual time=4.333..4.333 rows=7,306 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 629kB
10. 1.617 1.617 ↑ 1.0 7,306 1

Seq Scan on detected_objects (cost=0.00..167.06 rows=7,306 width=47) (actual time=0.005..1.617 rows=7,306 loops=1)

11.          

CTE points_with_objects

12. 0.178 75.860 ↓ 3.8 91 1

HashAggregate (cost=392.03..392.45 rows=24 width=548) (actual time=75.807..75.860 rows=91 loops=1)

  • Group Key: points_3.id
13. 2.061 75.682 ↑ 9.2 95 1

Hash Join (cost=203.16..385.45 rows=877 width=548) (actual time=44.661..75.682 rows=95 loops=1)

  • Hash Cond: (objects.point_id = (points_3.id)::text)
14. 33.885 33.885 ↑ 1.1 6,943 1

CTE Scan on objects (cost=0.00..146.12 rows=7,306 width=64) (actual time=4.362..33.885 rows=6,943 loops=1)

15. 0.456 39.736 ↓ 26.1 626 1

Hash (cost=202.86..202.86 rows=24 width=553) (actual time=39.736..39.736 rows=626 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 73kB
16. 0.739 39.280 ↓ 26.1 626 1

Nested Loop (cost=0.41..202.86 rows=24 width=553) (actual time=0.052..39.280 rows=626 loops=1)

17. 26.647 26.647 ↓ 26.1 626 1

CTE Scan on filtered_points points_3 (cost=0.00..0.48 rows=24 width=516) (actual time=0.001..26.647 rows=626 loops=1)

18. 11.894 11.894 ↑ 1.0 1 626

Index Only Scan using points_pkey on points points_4 (cost=0.41..8.43 rows=1 width=37) (actual time=0.019..0.019 rows=1 loops=626)

  • Index Cond: (id = (points_3.id)::text)
  • Heap Fetches: 626
19.          

CTE get_points

20. 176.040 266.120 ↓ 26.1 626 1

Hash Left Join (cost=0.78..210.77 rows=24 width=232) (actual time=76.292..266.120 rows=626 loops=1)

  • Hash Cond: ((points_5.id)::text = (points_with_objects_1.point_id)::text)
21. 0.370 0.370 ↓ 26.1 626 1

CTE Scan on filtered_points points_5 (cost=0.00..0.48 rows=24 width=674) (actual time=0.120..0.370 rows=626 loops=1)

22. 0.032 75.938 ↓ 3.8 91 1

Hash (cost=0.48..0.48 rows=24 width=516) (actual time=75.938..75.938 rows=91 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
23. 75.906 75.906 ↓ 3.8 91 1

CTE Scan on points_with_objects points_with_objects_1 (cost=0.00..0.48 rows=24 width=516) (actual time=75.808..75.906 rows=91 loops=1)

24.          

SubPlan (forHash Left Join)

25. 0.626 3.130 ↑ 1.0 1 626

Limit (cost=0.29..5.64 rows=1 width=11) (actual time=0.005..0.005 rows=1 loops=626)

26. 2.504 2.504 ↑ 3.0 1 626

Index Scan using idx_segment_stats_segment_id on segment_stats (cost=0.29..16.33 rows=3 width=11) (actual time=0.004..0.004 rows=1 loops=626)

  • Index Cond: (points_5.segment_id = segment_id)
27. 10.642 10.642 ↓ 0.0 0 626

CTE Scan on points_with_objects (cost=0.00..0.54 rows=1 width=32) (actual time=0.016..0.017 rows=0 loops=626)

  • Filter: ((point_id)::text = (points_5.id)::text)
  • Rows Removed by Filter: 91
Planning time : 1.363 ms
Execution time : 267.617 ms