explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bnN8

Settings
# exclusive inclusive rows x rows loops node
1. 0.096 467.917 ↓ 17.0 68 1

Nested Loop (cost=90,415.97..90,448.99 rows=4 width=58) (actual time=467.681..467.917 rows=68 loops=1)

2. 0.053 467.685 ↓ 17.0 68 1

HashAggregate (cost=90,415.68..90,415.72 rows=4 width=5) (actual time=467.661..467.685 rows=68 loops=1)

  • Group Key: ("ANY_subquery".dealer_zipcode)::text
3. 0.034 467.632 ↓ 17.0 68 1

Subquery Scan on ANY_subquery (cost=90,413.53..90,415.67 rows=4 width=5) (actual time=467.289..467.632 rows=68 loops=1)

4. 0.167 467.598 ↓ 17.0 68 1

Unique (cost=90,413.53..90,415.63 rows=4 width=5) (actual time=467.288..467.598 rows=68 loops=1)

5. 0.957 467.431 ↓ 1.6 662 1

Sort (cost=90,413.53..90,414.58 rows=419 width=5) (actual time=467.286..467.431 rows=662 loops=1)

  • Sort Key: v0.dealer_zipcode
  • Sort Method: quicksort Memory: 56kB
6. 7.166 466.474 ↓ 1.6 662 1

Hash Semi Join (cost=27,204.83..90,395.28 rows=419 width=5) (actual time=289.731..466.474 rows=662 loops=1)

  • Hash Cond: ((v0.dealer_zipcode)::text = (u0.zipcode)::text)
7. 177.001 447.707 ↓ 2.6 21,360 1

Bitmap Heap Scan on used_vehicles_usedcarinventory v0 (cost=25,904.34..89,068.15 rows=8,371 width=5) (actual time=276.562..447.707 rows=21,360 loops=1)

  • Recheck Cond: (model_id = ANY ('{1120,1282,2329,1197,1425,1145}'::integer[]))
  • Filter: ((NOT removed) AND ((condition)::text = 'Pre-Owned'::text) AND (lead_provider_id = ANY ('{1,2,3,4,6}'::integer[])) AND (year_id = ANY ('{1,2,3,33,30,31}'::integer[])))
  • Rows Removed by Filter: 10,183
  • Heap Blocks: exact=26,399
8. 50.285 270.706 ↓ 0.0 0 1

BitmapAnd (cost=25,904.34..25,904.34 rows=17,482 width=0) (actual time=270.706..270.706 rows=0 loops=1)

9. 85.025 85.025 ↓ 1.2 399,909 1

Bitmap Index Scan on used_vehicles_usedcarinventory_removed_eca6c46f (cost=0.00..9,426.01 rows=328,210 width=0) (actual time=85.025..85.025 rows=399,909 loops=1)

  • Index Cond: (removed = false)
10. 135.396 135.396 ↓ 1.2 560,727 1

Bitmap Index Scan on used_vehicles_usedcarinventory_477cbf8a (cost=0.00..16,473.89 rows=484,703 width=0) (actual time=135.396..135.396 rows=560,727 loops=1)

  • Index Cond: (model_id = ANY ('{1120,1282,2329,1197,1425,1145}'::integer[]))
11. 0.280 11.601 ↓ 5.1 869 1

Hash (cost=1,298.36..1,298.36 rows=171 width=6) (actual time=11.601..11.601 rows=869 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 41kB
12. 11.321 11.321 ↓ 5.1 869 1

Seq Scan on extralibs_zipcode u0 (cost=0.00..1,298.36 rows=171 width=6) (actual time=1.571..11.321 rows=869 loops=1)

  • Filter: ((longitude >= '-82.4986548'::numeric) AND (longitude <= '-77.6599452'::numeric) AND (latitude >= 24.0370452) AND (latitude <= 28.8757548))
  • Rows Removed by Filter: 40,049
13. 0.136 0.136 ↑ 1.0 1 68

Index Scan using extralibs_zipcode_zipcode_a84a4bfd_like on extralibs_zipcode (cost=0.29..8.31 rows=1 width=58) (actual time=0.002..0.002 rows=1 loops=68)

  • Index Cond: ((zipcode)::text = ("ANY_subquery".dealer_zipcode)::text)
Planning time : 0.729 ms
Execution time : 468.737 ms