explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sZ4N

Settings
# exclusive inclusive rows x rows loops node
1. 41.726 15,966.508 ↓ 0.0 0 1

Subquery Scan on f (cost=6,942.89..20,228.57 rows=1 width=111) (actual time=15,966.508..15,966.508 rows=0 loops=1)

  • Filter: (((NOT f.re) OR (NOT f.ad)) AND (f.geometry_index && '0101000020E6100000FE7854A1268F104075D88EF3484D4940'::geometry) AND (f.feature_type = ANY ('{1668,4435,1931,3280,2803,1929,11444,10350,1669,3697}'::integer[])) AND _st_intersects(f.geometry_index, '0101000020E6100000FE7854A1268F104075D88EF3484D4940'::geometry) AND (SubPlan 1))
  • Rows Removed by Filter: 4
2. 0.106 15,924.782 ↑ 4.2 4 1

WindowAgg (cost=6,942.89..6,944.30 rows=17 width=145) (actual time=15,924.710..15,924.782 rows=4 loops=1)

3. 0.033 15,924.676 ↑ 4.2 4 1

Sort (cost=6,942.89..6,942.94 rows=17 width=111) (actual time=15,924.673..15,924.676 rows=4 loops=1)

  • Sort Key: f_1.id
  • Sort Method: quicksort Memory: 25kB
4. 14,051.770 15,924.643 ↑ 4.2 4 1

Bitmap Heap Scan on feature f_1 (cost=58.20..6,942.55 rows=17 width=111) (actual time=4,949.340..15,924.643 rows=4 loops=1)

  • Recheck Cond: ((branch = '233b38a4-f0bf-4289-bfdc-7f2a04fc4ab3'::uuid) AND (version > '421022838'::bigint) AND (version <= '423208098'::bigint))
  • Rows Removed by Index Recheck: 17506400
  • Filter: ((geometry_index && '0101000020E6100000FE7854A1268F104075D88EF3484D4940'::geometry) AND (feature_type = ANY ('{1668,4435,1931,3280,2803,1929,11444,10350,1669,3697}'::integer[])))
  • Rows Removed by Filter: 15509863
  • Heap Blocks: exact=199940 lossy=1855900
5. 1,872.873 1,872.873 ↓ 8,965.2 15,509,867 1

Bitmap Index Scan on idx_feature_branch_version (cost=0.00..58.19 rows=1,730 width=0) (actual time=1,872.872..1,872.873 rows=15,509,867 loops=1)

  • Index Cond: ((branch = '233b38a4-f0bf-4289-bfdc-7f2a04fc4ab3'::uuid) AND (version > '421022838'::bigint) AND (version <= '423208098'::bigint))
6.          

SubPlan (for Subquery Scan)

7. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.57..781.00 rows=1 width=24) (never executed)

8. 0.000 0.000 ↓ 0.0 0

Index Only Scan Backward using feature_pkey on feature f1 (cost=0.57..7,024.47 rows=9 width=24) (never executed)

  • Index Cond: ((id = f.id) AND (branch = '233b38a4-f0bf-4289-bfdc-7f2a04fc4ab3'::uuid))
  • Filter: (((version + 0) > '421022838'::bigint) AND ((version + 0) <= '423208098'::bigint))
  • Heap Fetches: 0
Planning time : 0.485 ms
Execution time : 15,970.027 ms