explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

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

CTE Scan on filtered_points points (cost=3,083.68..3,293.34 rows=24 width=232) (actual time=31.191..218.822 rows=626 loops=1)

2.          

CTE selected_points

3. 24.660 24.660 ↓ 13.1 631 1

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

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

CTE filtered_points

5. 25.162 25.162 ↓ 26.1 626 1

CTE Scan on selected_points points_2 (cost=0.00..0.96 rows=24 width=951) (actual time=0.102..25.162 rows=626 loops=1)

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

CTE points_with_objects

7. 0.118 30.835 ↓ 3.8 91 1

GroupAggregate (cost=246.41..247.10 rows=24 width=548) (actual time=30.724..30.835 rows=91 loops=1)

  • Group Key: points_3.id
8. 0.495 30.717 ↓ 3.5 95 1

Sort (cost=246.41..246.47 rows=27 width=526) (actual time=30.708..30.717 rows=95 loops=1)

  • Sort Key: points_3.id
  • Sort Method: quicksort Memory: 35kB
9. 0.080 30.222 ↓ 3.5 95 1

Nested Loop (cost=1.20..245.76 rows=27 width=526) (actual time=26.507..30.222 rows=95 loops=1)

  • Join Filter: ((points_3.id)::text = (points_4.id)::text)
10. 1.837 28.432 ↓ 3.5 95 1

Hash Join (cost=0.78..195.51 rows=27 width=563) (actual time=26.468..28.432 rows=95 loops=1)

  • Hash Cond: (objects.point_id = (points_3.id)::text)
11. 0.960 0.960 ↑ 1.0 7,306 1

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

12. 0.291 25.635 ↓ 26.1 626 1

Hash (cost=0.48..0.48 rows=24 width=516) (actual time=25.634..25.635 rows=626 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 51kB
13. 25.344 25.344 ↓ 26.1 626 1

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

14. 1.710 1.710 ↑ 1.0 1 95

Index Only Scan using points_pkey on points points_4 (cost=0.41..1.85 rows=1 width=37) (actual time=0.018..0.018 rows=1 loops=95)

  • Index Cond: (id = objects.point_id)
  • Heap Fetches: 95
15.          

SubPlan (forCTE Scan)

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

17. 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)
18. 41.316 41.316 ↓ 0.0 0 626

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

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