explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QJMP : Optimized new poiints in area code

Settings

Optimization path:

Optimization(s) for this plan:

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

CTE Scan on get_points (cost=6,831.76..6,832.24 rows=24 width=232) (actual time=96.727..279.422 rows=626 loops=1)

2.          

CTE selected_points

3. 40.606 40.606 ↓ 13.1 631 1

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

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

CTE filtered_points

5. 41.575 41.575 ↓ 26.1 626 1

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

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

CTE objects

7. 16.468 31.565 ↑ 1.1 6,943 1

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

  • Hash Cond: ((points_2.id)::text = detected_objects.point_id)
8. 8.094 8.094 ↑ 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.094 rows=47,570 loops=1)

9. 3.619 7.003 ↑ 1.0 7,306 1

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

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

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

11.          

CTE points_with_objects

12. 0.213 96.205 ↓ 3.8 91 1

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

  • Group Key: points_3.id
13. 2.026 95.992 ↑ 9.2 95 1

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

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

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

15. 0.526 58.070 ↓ 26.1 626 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 73kB
16. 0.416 57.544 ↓ 26.1 626 1

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

17. 42.104 42.104 ↓ 26.1 626 1

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

18. 15.024 15.024 ↑ 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.024..0.024 rows=1 loops=626)

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

CTE get_points

20. 177.411 277.864 ↓ 26.1 626 1

Hash Left Join (cost=0.78..197.81 rows=24 width=232) (actual time=96.724..277.864 rows=626 loops=1)

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

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

22. 0.036 96.308 ↓ 3.8 91 1

Hash (cost=0.48..0.48 rows=24 width=548) (actual time=96.308..96.308 rows=91 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
23. 96.272 96.272 ↓ 3.8 91 1

CTE Scan on points_with_objects (cost=0.00..0.48 rows=24 width=548) (actual time=96.155..96.272 rows=91 loops=1)

24.          

SubPlan (forHash Left Join)

25. 0.626 3.756 ↑ 1.0 1 626

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

26. 3.130 3.130 ↑ 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.005..0.005 rows=1 loops=626)

  • Index Cond: (points_5.segment_id = segment_id)
Planning time : 1.657 ms
Execution time : 280.227 ms