explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sVGL

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 0.313 ↑ 1.0 1 1

Limit (cost=112.99..114.38 rows=1 width=40) (actual time=0.310..0.313 rows=1 loops=1)

2.          

CTE pt

3. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)

4. 0.002 0.309 ↑ 80.0 1 1

Nested Loop (cost=112.98..223.80 rows=80 width=40) (actual time=0.308..0.309 rows=1 loops=1)

5. 0.000 0.169 ↑ 1.0 1 1

Limit (cost=112.70..112.74 rows=1 width=64) (actual time=0.169..0.169 rows=1 loops=1)

6. 0.001 0.169 ↑ 2.0 1 1

Append (cost=112.70..112.77 rows=2 width=64) (actual time=0.169..0.169 rows=1 loops=1)

7. 0.000 0.168 ↑ 1.0 1 1

Subquery Scan on stream_closest_pt (cost=112.70..112.72 rows=1 width=46) (actual time=0.168..0.168 rows=1 loops=1)

8. 0.001 0.168 ↑ 1.0 1 1

Limit (cost=112.70..112.71 rows=1 width=56) (actual time=0.167..0.168 rows=1 loops=1)

9. 0.029 0.167 ↑ 1.0 1 1

Sort (cost=112.70..112.71 rows=1 width=56) (actual time=0.167..0.167 rows=1 loops=1)

  • Sort Key: streams.stream_order DESC, (st_distance(streams.geom, pt.loc))
  • Sort Method: quicksort Memory: 25kB
10. 0.024 0.138 ↓ 2.0 2 1

Nested Loop (cost=4.48..112.69 rows=1 width=56) (actual time=0.114..0.138 rows=2 loops=1)

11. 0.004 0.004 ↑ 1.0 1 1

CTE Scan on pt (cost=0.00..0.02 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=1)

12. 0.037 0.110 ↓ 2.0 2 1

Bitmap Heap Scan on wcab_streams streams (cost=4.48..112.60 rows=1 width=556) (actual time=0.100..0.110 rows=2 loops=1)

  • Recheck Cond: (geom && st_expand(pt.loc, ''0.0100000000000000002''::double precision))
  • Filter: ((pt.loc && st_expand(geom, ''0.0100000000000000002''::double precision)) AND _st_dwithin(geom, pt.loc, ''0.0100000000000000002''::double precision))
  • Heap Blocks: exact=2
13. 0.073 0.073 ↑ 13.0 2 1

Bitmap Index Scan on wcab_streams_geom_idx (cost=0.00..4.48 rows=26 width=0) (actual time=0.073..0.073 rows=2 loops=1)

  • Index Cond: (geom && st_expand(pt.loc, ''0.0100000000000000002''::double precision))
14. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 2 (cost=0.00..0.03 rows=1 width=64) (never executed)

15. 0.000 0.000 ↓ 0.0 0

CTE Scan on pt pt_1 (cost=0.00..0.02 rows=1 width=64) (never executed)

16. 0.138 0.138 ↑ 8.0 1 1

Index Scan using wcab_ffwtr_d_geom_gist on wcab_ffwtr (cost=0.28..110.97 rows=8 width=1,721) (actual time=0.138..0.138 rows=1 loops=1)

  • Index Cond: (stream_closest_pt.pt_on_line_geom && geom)
  • Filter: (include AND _st_intersects(stream_closest_pt.pt_on_line_geom, geom))
  • Rows Removed by Filter: 1
Planning time : 0.608 ms
Execution time : 0.647 ms