explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oMwjx

Settings
# exclusive inclusive rows x rows loops node
1. 0.853 124,911.155 ↓ 2.7 133 1

Hash Right Join (cost=31,180.28..31,688.52 rows=49 width=140) (actual time=3,965.327..124,911.155 rows=133 loops=1)

  • Hash Cond: (drivetimes.co_id = l.id)
2. 0.985 2,932.732 ↑ 1.5 133 1

HashAggregate (cost=2,171.06..2,173.56 rows=200 width=36) (actual time=2,932.310..2,932.732 rows=133 loops=1)

  • Group Key: drivetimes.co_id
3. 0.261 2,931.747 ↑ 3.2 3,473 1

Nested Loop (cost=2.25..2,115.50 rows=11,111 width=8) (actual time=2,915.478..2,931.747 rows=3,473 loops=1)

4. 0.039 0.137 ↑ 100.0 1 1

HashAggregate (cost=2.00..3.25 rows=100 width=72) (actual time=0.136..0.137 rows=1 loops=1)

  • Group Key: t.travel_mode, t.travel_time, t.travel_time_min
5. 0.098 0.098 ↓ 1.3 133 1

Function Scan on jsonb_to_recordset t (cost=0.00..1.00 rows=100 width=44) (actual time=0.090..0.098 rows=133 loops=1)

6. 2,931.349 2,931.349 ↓ 31.3 3,473 1

Function Scan on rf_vz_drivetimes drivetimes (cost=0.25..20.25 rows=111 width=16) (actual time=2,915.340..2,931.349 rows=3,473 loops=1)

  • Filter: (((t.travel_time_min)::double precision <= drivetime) AND (drivetime <= (t.travel_time)::double precision))
  • Rows Removed by Filter: 141557
7. 0.018 56.869 ↓ 2.7 133 1

Hash (cost=29,008.61..29,008.61 rows=49 width=4) (actual time=56.869..56.869 rows=133 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
8. 0.010 56.851 ↓ 2.7 133 1

Subquery Scan on l (cost=29,007.88..29,008.61 rows=49 width=4) (actual time=56.803..56.851 rows=133 loops=1)

9. 0.007 56.841 ↓ 2.7 133 1

Unique (cost=29,007.88..29,008.12 rows=49 width=164) (actual time=56.803..56.841 rows=133 loops=1)

10.          

Initplan (for Unique)

11. 0.017 0.017 ↑ 1.0 1 1

Seq Scan on rtad (cost=0.00..4.06 rows=1 width=4) (actual time=0.005..0.017 rows=1 loops=1)

  • Filter: ((alias)::text = 'active'::text)
  • Rows Removed by Filter: 89
12. 0.049 56.817 ↓ 5.4 266 1

Sort (cost=29,003.81..29,003.94 rows=49 width=164) (actual time=56.802..56.817 rows=266 loops=1)

  • Sort Key: l_1.id
  • Sort Method: quicksort Memory: 37kB
13. 0.085 56.768 ↓ 5.4 266 1

Nested Loop (cost=21,063.51..29,002.44 rows=49 width=164) (actual time=51.859..56.768 rows=266 loops=1)

14. 0.519 56.417 ↓ 5.4 266 1

Hash Join (cost=21,063.22..28,932.38 rows=49 width=8) (actual time=51.835..56.417 rows=266 loops=1)

  • Hash Cond: ("*SELECT* 1".co_id = c.lo_id)
15. 0.354 55.805 ↓ 4.3 7,500 1

Append (cost=20,974.13..28,836.23 rows=1,751 width=4) (actual time=51.738..55.805 rows=7,500 loops=1)

16. 0.489 55.429 ↓ 4.3 7,500 1

Subquery Scan on *SELECT* 1 (cost=20,974.13..21,083.14 rows=1,750 width=4) (actual time=51.738..55.429 rows=7,500 loops=1)

17. 2.290 54.940 ↓ 4.3 7,500 1

Merge Join (cost=20,974.13..21,065.64 rows=1,750 width=60) (actual time=51.738..54.940 rows=7,500 loops=1)

  • Merge Cond: ((rtsepecoav.co_id = act.co_id) AND (rtsepecoav.se_id = act.se_id))
18. 2.062 4.442 ↓ 3.1 7,500 1

Sort (cost=8,427.55..8,433.57 rows=2,409 width=12) (actual time=4.001..4.442 rows=7,500 loops=1)

  • Sort Key: rtsepecoav.co_id, rtsepecoav.se_id
  • Sort Method: quicksort Memory: 544kB
19. 2.380 2.380 ↓ 3.1 7,500 1

Index Scan using rtsepecoav_unique on rtsepecoav (cost=0.43..8,292.23 rows=2,409 width=12) (actual time=0.031..2.380 rows=7,500 loops=1)

  • Index Cond: ((ad_id = $2) AND (se_id = ANY ('{2999,3000}'::integer[])) AND (pe_id = 6))
  • Filter: (value_num = '1'::double precision)
20. 2.234 48.208 ↓ 1.0 7,500 1

Sort (cost=12,546.57..12,565.22 rows=7,461 width=12) (actual time=47.733..48.208 rows=7,500 loops=1)

  • Sort Key: act.co_id, act.se_id
  • Sort Method: quicksort Memory: 544kB
21. 45.974 45.974 ↓ 1.0 7,500 1

Seq Scan on rmv_active_sepeco act (cost=0.00..12,066.64 rows=7,461 width=12) (actual time=0.013..45.974 rows=7,500 loops=1)

  • Filter: (pe_id = 6)
  • Rows Removed by Filter: 652791
22. 0.001 0.022 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=1.28..7,753.09 rows=1 width=4) (actual time=0.022..0.022 rows=0 loops=1)

23. 0.001 0.021 ↓ 0.0 0 1

Nested Loop (cost=1.28..7,753.08 rows=1 width=60) (actual time=0.021..0.021 rows=0 loops=1)

24. 0.000 0.020 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.85..7,751.75 rows=1 width=12) (actual time=0.020..0.020 rows=0 loops=1)

  • Join Filter: (av_1.pe_id = rtpe.id)
25. 0.006 0.020 ↓ 0.0 0 1

Nested Loop (cost=0.43..1,637.77 rows=2,345 width=16) (actual time=0.020..0.020 rows=0 loops=1)

26. 0.013 0.013 ↑ 1.0 1 1

Seq Scan on rtpe (cost=0.00..3.04 rows=1 width=8) (actual time=0.010..0.013 rows=1 loops=1)

  • Filter: (id = 6)
  • Rows Removed by Filter: 87
27. 0.001 0.001 ↓ 0.0 0 1

Index Scan using rtsepecoav_unique on rtsepecoav av (cost=0.43..1,630.75 rows=398 width=16) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: ((ad_id = $2) AND (se_id = ANY ('{2999,3000}'::integer[])) AND (pe_id = rtpe.baseline_id))
  • Filter: (value_num = '1'::double precision)
28. 0.000 0.000 ↓ 0.0 0

Index Scan using rtsepecoav_ad_id_pe_id_co_id_index on rtsepecoav av_1 (cost=0.43..2.59 rows=1 width=16) (never executed)

  • Index Cond: ((ad_id = av.ad_id) AND (ad_id = $2) AND (pe_id = 6) AND (co_id = av.co_id))
  • Filter: (se_id = av.se_id)
29. 0.000 0.000 ↓ 0.0 0

Index Only Scan using unique_rmv_active_sepeco on rmv_active_sepeco act_1 (cost=0.42..1.31 rows=1 width=12) (never executed)

  • Index Cond: ((se_id = av.se_id) AND (pe_id = 6) AND (co_id = av.co_id))
  • Heap Fetches: 0
30. 0.023 0.093 ↓ 1.1 220 1

Hash (cost=86.49..86.49 rows=208 width=4) (actual time=0.093..0.093 rows=220 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
31. 0.051 0.070 ↓ 1.1 220 1

Bitmap Heap Scan on rtco c (cost=9.89..86.49 rows=208 width=4) (actual time=0.024..0.070 rows=220 loops=1)

  • Recheck Cond: (br_id = 1062)
  • Heap Blocks: exact=16
32. 0.019 0.019 ↓ 1.1 220 1

Bitmap Index Scan on rtco_br_id_idx (cost=0.00..9.84 rows=208 width=0) (actual time=0.019..0.019 rows=220 loops=1)

  • Index Cond: (br_id = 1062)
33. 0.266 0.266 ↑ 1.0 1 266

Index Only Scan using rtlo_pkey on rtlo l_1 (cost=0.29..1.42 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=266)

  • Index Cond: (id = "*SELECT* 1".co_id)
  • Heap Fetches: 266
34.          

SubPlan (for Hash Right Join)

35. 121,920.701 121,920.701 ↑ 1,000.0 1 133

Function Scan on rf_kpi_sepezoco_other_storesales (cost=0.26..10.26 rows=1,000 width=8) (actual time=916.697..916.697 rows=1 loops=133)

Planning time : 1.848 ms
Execution time : 124,913.085 ms