explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

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

CTE Scan on filtered_points points (cost=6,633.95..6,843.61 rows=24 width=232) (actual time=75.813..264.682 rows=626 loops=1)

2.          

CTE selected_points

3. 24.990 24.990 ↓ 13.1 631 1

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

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

CTE filtered_points

5. 25.600 25.600 ↓ 26.1 626 1

CTE Scan on selected_points points_2 (cost=0.00..0.96 rows=24 width=951) (actual time=0.107..25.600 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.453 30.412 ↑ 1.1 6,943 1

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

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

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

9. 2.847 4.552 ↑ 1.0 7,306 1

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

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

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

11.          

CTE points_with_objects

12. 0.175 75.445 ↓ 3.8 91 1

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

  • Group Key: points_4.id
13. 2.066 75.270 ↑ 9.2 95 1

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

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

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

15. 0.530 39.263 ↓ 26.1 626 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 73kB
16. 0.458 38.733 ↓ 26.1 626 1

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

17. 25.755 25.755 ↓ 26.1 626 1

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

18. 12.520 12.520 ↑ 1.0 1 626

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

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

SubPlan (forCTE Scan)

20. 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)

21. 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.segment_id = segment_id)
22. 85.762 85.762 ↓ 0.0 0 626

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

  • Filter: ((point_id)::text = (points.id)::text)
  • Rows Removed by Filter: 91
Planning time : 1.196 ms
Execution time : 265.233 ms