explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QGnJ : Add some indexing to: evalution of vi_get_points

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 259.203 272.563 ↓ 16.2 779 1

CTE Scan on filtered_points points (cost=771.18..1,248.63 rows=48 width=232) (actual time=9.773..272.563 rows=779 loops=1)

2.          

CTE selected_points

3. 3.235 4.231 ↓ 9.2 1,784 1

Bitmap Heap Scan on points points_1 (cost=9.79..702.16 rows=194 width=361) (actual time=1.236..4.231 rows=1,784 loops=1)

  • Recheck Cond: ('0103000000010000000500000048E17A14AEC72840068195438BAC494048E17A14AEC72840EC51B81E85AB4940C3F5285C8FC22840EC51B81E85AB4940C3F5285C8FC22840068195438BAC494048E17A14AEC72840068195438BAC4940'::geometry ~ offset_geom)
  • Heap Blocks: exact=1213
4. 0.996 0.996 ↓ 9.2 1,784 1

Bitmap Index Scan on point_geom_idx (cost=0.00..9.74 rows=194 width=0) (actual time=0.995..0.996 rows=1,784 loops=1)

  • Index Cond: ('0103000000010000000500000048E17A14AEC72840068195438BAC494048E17A14AEC72840EC51B81E85AB4940C3F5285C8FC22840EC51B81E85AB4940C3F5285C8FC22840068195438BAC494048E17A14AEC72840068195438BAC4940'::geometry ~ offset_geom)
5.          

CTE filtered_points

6. 6.314 6.314 ↓ 16.2 779 1

CTE Scan on selected_points points_2 (cost=0.00..3.88 rows=48 width=959) (actual time=1.261..6.314 rows=779 loops=1)

  • Filter: ((offset_geom IS NOT NULL) AND (is_duplicate IS FALSE) AND (is_latest IS TRUE))
  • Rows Removed by Filter: 1005
7.          

CTE points_with_objects

8. 0.005 7.903 ↓ 0.0 0 1

HashAggregate (cost=62.50..63.10 rows=48 width=548) (actual time=7.903..7.903 rows=0 loops=1)

  • Group Key: points_3.id
9. 0.036 7.898 ↓ 0.0 0 1

Hash Join (cost=3.12..61.97 rows=105 width=526) (actual time=7.898..7.898 rows=0 loops=1)

  • Hash Cond: (detected_objects.point_id = (points_3.id)::text)
10. 0.607 1.945 ↓ 0.0 0 1

Hash Semi Join (cost=1.56..59.97 rows=105 width=563) (actual time=1.945..1.945 rows=0 loops=1)

  • Hash Cond: (detected_objects.point_id = (filtered_points.id)::text)
11. 0.398 0.398 ↑ 1.0 2,316 1

Seq Scan on detected_objects (cost=0.00..51.16 rows=2,316 width=47) (actual time=0.027..0.398 rows=2,316 loops=1)

12. 0.333 0.940 ↓ 16.2 779 1

Hash (cost=0.96..0.96 rows=48 width=516) (actual time=0.939..0.940 rows=779 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 61kB
13. 0.607 0.607 ↓ 16.2 779 1

CTE Scan on filtered_points (cost=0.00..0.96 rows=48 width=516) (actual time=0.001..0.607 rows=779 loops=1)

14. 0.347 5.917 ↓ 16.2 779 1

Hash (cost=0.96..0.96 rows=48 width=516) (actual time=5.917..5.917 rows=779 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 61kB
15. 5.570 5.570 ↓ 16.2 779 1

CTE Scan on filtered_points points_3 (cost=0.00..0.96 rows=48 width=516) (actual time=0.004..5.570 rows=779 loops=1)

16.          

CTE detected_ids

17. 7.906 7.906 ↓ 0.0 0 1

CTE Scan on points_with_objects (cost=0.00..0.96 rows=48 width=516) (actual time=7.905..7.906 rows=0 loops=1)

18.          

SubPlan (forCTE Scan)

19. 0.779 5.453 ↑ 1.0 1 779

Limit (cost=0.29..6.30 rows=1 width=9) (actual time=0.007..0.007 rows=1 loops=779)

20. 4.674 4.674 ↑ 2.0 1 779

Index Scan using idx_segment_stats_segment_id on segment_stats (cost=0.29..12.31 rows=2 width=9) (actual time=0.006..0.006 rows=1 loops=779)

  • Index Cond: (points.segment_id = segment_id)
21. 7.907 7.907 ↓ 0.0 0 1

CTE Scan on detected_ids (cost=0.00..0.96 rows=48 width=516) (actual time=7.907..7.907 rows=0 loops=1)

22. 0.000 0.000 ↓ 0.0 0

CTE Scan on points_with_objects objects (cost=0.00..1.08 rows=1 width=32) (never executed)

  • Filter: ((points.id)::text = (point_id)::text)
Planning time : 4.604 ms
Execution time : 273.189 ms