explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 41ym

Settings
# exclusive inclusive rows x rows loops node
1. 0.031 3,749.557 ↑ 1.0 8 1

Nested Loop Left Join (cost=5,081.73..40,654.18 rows=8 width=56) (actual time=2,734.809..3,749.557 rows=8 loops=1)

2. 0.022 0.022 ↑ 1.0 8 1

Values Scan on "*VALUES*" (cost=0.00..0.10 rows=8 width=32) (actual time=0.003..0.022 rows=8 loops=1)

3. 0.048 3,749.504 ↑ 1.0 1 8

Aggregate (cost=5,081.73..5,081.74 rows=1 width=24) (actual time=468.687..468.688 rows=1 loops=8)

4. 0.016 3,749.456 ↓ 0.0 0 8

Limit (cost=4,958.98..5,081.70 rows=1 width=3,605) (actual time=468.682..468.682 rows=0 loops=8)

5. 0.008 3,749.440 ↓ 0.0 0 8

Nested Loop Left Join (cost=4,958.98..6,308.90 rows=11 width=3,605) (actual time=468.680..468.680 rows=0 loops=8)

  • Join Filter: false
6. 0.016 3,749.432 ↓ 0.0 0 8

Nested Loop Left Join (cost=4,958.98..6,308.79 rows=11 width=32) (actual time=468.679..468.679 rows=0 loops=8)

  • Join Filter: false
7. 0.008 3,749.416 ↓ 0.0 0 8

Nested Loop Left Join (cost=4,958.98..6,308.68 rows=11 width=32) (actual time=468.677..468.677 rows=0 loops=8)

8. 0.008 3,749.408 ↓ 0.0 0 8

Nested Loop Left Join (cost=4,958.57..6,293.18 rows=10 width=97) (actual time=468.676..468.676 rows=0 loops=8)

  • Join Filter: (main.std_landuse = cmbtm.lu_code)
9. 13.920 3,749.400 ↓ 0.0 0 8

Append (cost=4,958.57..6,237.04 rows=10 width=102) (actual time=468.675..468.675 rows=0 loops=8)

10. 1,251.312 3,735.480 ↓ 0.0 0 8

Bitmap Heap Scan on property_master_search_v4_partitioned_4_19 main (cost=4,958.57..6,236.99 rows=10 width=102) (actual time=466.935..466.935 rows=0 loops=8)

  • Recheck Cond: ((center && '0106000020E61000000100000001030000000100000005000000000000C07B6F58C09B24D830555D3E40000000C07B6F58C08A7016874A4A3E40010000704C6758C08A7016874A4A3E40010000704C6758C09B24D830555D3E40000000C07B6F58C09B24D830555D3E40'::geometry) AND (gid = ANY ('{322770,322776}'::integer[])))
  • Filter: ((pid = 4) AND ("left"(geohash, 3) = "*VALUES*".column1) AND _st_intersects(center, '0106000020E61000000100000001030000000100000005000000000000C07B6F58C09B24D830555D3E40000000C07B6F58C08A7016874A4A3E40010000704C6758C08A7016874A4A3E40010000704C6758C09B24D830555D3E40000000C07B6F58C09B24D830555D3E40'::geometry))
  • Rows Removed by Filter: 38421
  • Heap Blocks: exact=219216
11. 18.928 2,484.168 ↓ 0.0 0 8

BitmapAnd (cost=4,958.57..4,958.57 rows=933 width=0) (actual time=310.521..310.521 rows=0 loops=8)

12. 484.896 484.896 ↓ 1.2 38,512 8

Bitmap Index Scan on property_master_search_v4_partitioned_4_19_center_idx (cost=0.00..781.35 rows=33,431 width=0) (actual time=60.612..60.612 rows=38,512 loops=8)

  • Index Cond: (center && '0106000020E61000000100000001030000000100000005000000000000C07B6F58C09B24D830555D3E40000000C07B6F58C08A7016874A4A3E40010000704C6758C08A7016874A4A3E40010000704C6758C09B24D830555D3E40000000C07B6F58C09B24D830555D3E40'::geometry)
13. 1,980.344 1,980.344 ↑ 1.0 204,794 8

Bitmap Index Scan on property_master_search_v4_partitioned_4_19_gid_idx (cost=0.00..4,176.97 rows=206,100 width=0) (actual time=247.543..247.543 rows=204,794 loops=8)

  • Index Cond: (gid = ANY ('{322770,322776}'::integer[]))
14. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..7.90 rows=327 width=4) (never executed)

15. 0.000 0.000 ↓ 0.0 0

Seq Scan on reference_commercial_building_type_mapping cmbtm (cost=0.00..6.27 rows=327 width=4) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Append (cost=0.41..1.54 rows=1 width=66) (never executed)

17. 0.000 0.000 ↓ 0.0 0

Index Only Scan using "reso_listings_latest_abor__mls__locationId_key" on reso_listings_latest_abor l (cost=0.41..1.54 rows=1 width=66) (never executed)

  • Index Cond: ((_mls = 'abor'::text) AND ("_locationId" = main.remineid))
  • Heap Fetches: 0
18. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.00 rows=0 width=0) (never executed)

  • One-Time Filter: false
19. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.00 rows=0 width=0) (never executed)

  • One-Time Filter: false
Planning time : 79.679 ms
Execution time : 3,751.899 ms