explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nSHC

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 0.218 ↑ 1.0 1 1

Limit (cost=112.99..114.38 rows=1 width=40) (actual time=0.216..0.218 rows=1 loops=1)

2.          

CTE pt

3. 0.001 0.001 ↑ 1.0 1 1

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

4. 0.001 0.215 ↑ 80.0 1 1

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

5. 0.000 0.123 ↑ 1.0 1 1

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

6. 0.001 0.123 ↑ 2.0 1 1

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

7. 0.001 0.122 ↑ 1.0 1 1

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

8. 0.000 0.121 ↑ 1.0 1 1

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

9. 0.008 0.121 ↑ 1.0 1 1

Sort (cost=112.70..112.71 rows=1 width=56) (actual time=0.121..0.121 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.113 ↓ 2.0 2 1

Nested Loop (cost=4.48..112.69 rows=1 width=56) (actual time=0.088..0.113 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.003..0.004 rows=1 loops=1)

12. 0.038 0.085 ↓ 2.0 2 1

Bitmap Heap Scan on wcab_streams streams (cost=4.48..112.60 rows=1 width=556) (actual time=0.075..0.085 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.047 0.047 ↑ 13.0 2 1

Bitmap Index Scan on wcab_streams_geom_idx (cost=0.00..4.48 rows=26 width=0) (actual time=0.047..0.047 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.091 0.091 ↑ 8.0 1 1

Index Scan using wcab_ffwtr_d_geom_gist on wcab_ffwtr ws (cost=0.28..110.97 rows=8 width=1,721) (actual time=0.091..0.091 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.463 ms
Execution time : 0.416 ms