explain.depesz.com

PostgreSQL's explain analyze made readable

Result: I5uU

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=139,983.06..139,983.88 rows=327 width=1,144) (actual rows= loops=)

  • Sort Key: a.id, a.time_modified, a.sid
2. 0.000 0.000 ↓ 0.0

Subquery Scan on a (cost=139,956.33..139,969.41 rows=327 width=1,144) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

HashAggregate (cost=139,956.33..139,959.60 rows=327 width=1,136) (actual rows= loops=)

  • Group Key: p.id, p.expiration_time, p.lat, p.lon, p.time_created, p.time_modified, p.time_used, p.result_type, (NULL::bigint), (NULL::character varying), dai.device_id, p.pass_id, p.sid
4. 0.000 0.000 ↓ 0.0

Append (cost=0.29..139,945.70 rows=327 width=1,136) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.29..18.67 rows=1 width=1,136) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Seq Scan on passdenialhistory p (cost=0.00..10.35 rows=1 width=1,096) (actual rows= loops=)

  • Filter: ((date(timezone('EST5EDT'::text, timezone('UTC'::text, time_modified))) >= to_date('19990101'::text, 'YYYYMMDD'::text)) AND (date(timezone('EST5EDT'::text, timezone('UTC'::text, time_modified))) <= to_date('99990101'::text, 'YYYYMMDD'::text)))
7. 0.000 0.000 ↓ 0.0

Index Scan using deviceappinstallation_pkey on deviceappinstallation dai (cost=0.29..8.31 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (p.device_app_installation_id = id)
8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=209.84..11,760.82 rows=65 width=101) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=209.41..11,754.00 rows=1 width=109) (actual rows= loops=)

  • Join Filter: (ch.sid = (SubPlan 5))
10. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=209.28..11,751.34 rows=1 width=100) (actual rows= loops=)

  • Join Filter: (cu.sid = (SubPlan 4))
11. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=208.85..11,734.74 rows=1 width=92) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Merge Join (cost=208.57..11,734.42 rows=1 width=92) (actual rows= loops=)

  • Merge Cond: (pu.pass_exception_id = px.id)
  • Join Filter: ((SubPlan 3) = px.sid)
13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.85..1,245,660.30 rows=8,731 width=92) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Index Scan using idx_pu_pexid on passuse pu (cost=0.42..57,285.47 rows=1,004,710 width=40) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Index Scan using idx_pacth_puid on passactivityhistory p_1 (cost=0.43..1.17 rows=1 width=68) (actual rows= loops=)

  • Index Cond: (pass_use_id = pu.id)
  • Filter: ((type = ANY ('{14000,14002,14003}'::integer[])) AND (date(timezone('EST5EDT'::text, timezone('UTC'::text, time_modified))) >= to_date('19990101'::text, 'YYYYMMDD'::text)) AND (date(timezone('EST5EDT'::text, timezone('UTC'::text, time_modified))) <= to_date('99990101'::text, 'YYYYMMDD'::text)))
16. 0.000 0.000 ↓ 0.0

Index Scan using idx_pexh_id on passexceptionhistory px (cost=0.29..6,531.46 rows=87,267 width=20) (actual rows= loops=)

17.          

SubPlan (forMerge Join)

18. 0.000 0.000 ↓ 0.0

Limit (cost=8.34..8.34 rows=1 width=12) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Sort (cost=8.34..8.34 rows=1 width=12) (actual rows= loops=)

  • Sort Key: x.time_modified DESC
20. 0.000 0.000 ↓ 0.0

Index Scan using idx_pexh_id on passexceptionhistory x (cost=0.29..8.33 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (id = pu.pass_exception_id)
  • Filter: (date(timezone('EST5EDT'::text, timezone('UTC'::text, time_modified))) <= date(timezone('EST5EDT'::text, timezone('UTC'::text, pu.time_modified))))
21. 0.000 0.000 ↓ 0.0

Index Scan using accesstoken_pkey on accesstoken a_1 (cost=0.28..0.31 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (p_1.access_token_id = id)
22. 0.000 0.000 ↓ 0.0

Index Scan using idx_cu_id on clientuserhistory cu (cost=0.43..8.11 rows=1 width=28) (actual rows= loops=)

  • Index Cond: (id = a_1.client_user_id)
23.          

SubPlan (forNested Loop Left Join)

24. 0.000 0.000 ↓ 0.0

Limit (cost=8.47..8.48 rows=1 width=12) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Sort (cost=8.47..8.48 rows=1 width=12) (actual rows= loops=)

  • Sort Key: e_1.time_modified DESC
26. 0.000 0.000 ↓ 0.0

Index Scan using idx_cu_id on clientuserhistory e_1 (cost=0.43..8.46 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (id = a_1.client_user_id)
  • Filter: (date(timezone('EST5EDT'::text, timezone('UTC'::text, time_modified))) <= date(timezone('EST5EDT'::text, timezone('UTC'::text, pu.time_modified))))
27. 0.000 0.000 ↓ 0.0

Index Scan using clienthistory_id_time_modified_idx on clienthistory ch (cost=0.14..0.18 rows=1 width=29) (actual rows= loops=)

  • Index Cond: (id = cu.client_id)
  • Filter: ((name)::text = 'Bytemark Merchant Mobile'::text)
28.          

SubPlan (forNested Loop Left Join)

29. 0.000 0.000 ↓ 0.0

Limit (cost=2.46..2.47 rows=1 width=12) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Sort (cost=2.46..2.47 rows=1 width=12) (actual rows= loops=)

  • Sort Key: i_1.time_modified DESC
31. 0.000 0.000 ↓ 0.0

Seq Scan on clienthistory i_1 (cost=0.00..2.46 rows=1 width=12) (actual rows= loops=)

  • Filter: ((id = cu.client_id) AND ((name)::text = 'Bytemark Merchant Mobile'::text) AND (date(timezone('EST5EDT'::text, timezone('UTC'::text, time_modified))) <= date(timezone('EST5EDT'::text, timezone('UTC'::text, pu.time_modified)))))
32. 0.000 0.000 ↓ 0.0

Index Scan using deviceappinstallationhistory_id_time_modified_idx on deviceappinstallationhistory dai_1 (cost=0.43..4.81 rows=202 width=24) (actual rows= loops=)

  • Index Cond: ((pu.device_app_installation_id = id) AND (pu.time_modified >= time_modified))
33. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.72..128,162.94 rows=261 width=101) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.29..127,987.75 rows=4 width=109) (actual rows= loops=)

  • Join Filter: ((ch_1.id = cu_1.client_id) AND (ch_1.sid = (SubPlan 2)))
35. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.29..127,975.62 rows=4 width=108) (actual rows= loops=)

  • Join Filter: (cu_1.sid = (SubPlan 1))
36. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.86..127,909.21 rows=4 width=100) (actual rows= loops=)

  • Join Filter: (p_2.access_token_id = a_2.id)
37. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.86..127,822.57 rows=4 width=100) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..127,111.97 rows=87 width=92) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Seq Scan on passusehistory pu_1 (cost=0.00..71,330.23 rows=7,745 width=40) (actual rows= loops=)

  • Filter: (date_part('year'::text, start_date) = '1900'::double precision)
40. 0.000 0.000 ↓ 0.0

Index Scan using idx_pacth_puid on passactivityhistory p_2 (cost=0.43..7.19 rows=1 width=68) (actual rows= loops=)

  • Index Cond: (pass_use_id = pu_1.id)
  • Filter: ((type = ANY ('{14000,14002,14003}'::integer[])) AND (date(timezone('EST5EDT'::text, timezone('UTC'::text, time_modified))) >= to_date('19990101'::text, 'YYYYMMDD'::text)) AND (date(timezone('EST5EDT'::text, timezone('UTC'::text, time_modified))) <= to_date('99990101'::text, 'YYYYMMDD'::text)))
41. 0.000 0.000 ↓ 0.0

Index Scan using idx_passevent_id on passeventhistory pe (cost=0.43..8.16 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (id = pu_1.pass_event_id)
  • Filter: (date_part('year'::text, start_date) = '1900'::double precision)
42. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..36.96 rows=864 width=16) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Seq Scan on accesstoken a_2 (cost=0.00..32.64 rows=864 width=16) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Index Scan using idx_cu_id on clientuserhistory cu_1 (cost=0.43..8.11 rows=1 width=28) (actual rows= loops=)

  • Index Cond: (id = a_2.client_user_id)
45.          

SubPlan (forNested Loop Left Join)

46. 0.000 0.000 ↓ 0.0

Limit (cost=8.47..8.48 rows=1 width=12) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Sort (cost=8.47..8.48 rows=1 width=12) (actual rows= loops=)

  • Sort Key: e.time_modified DESC
48. 0.000 0.000 ↓ 0.0

Index Scan using idx_cu_id on clientuserhistory e (cost=0.43..8.46 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (id = a_2.client_user_id)
  • Filter: (date(timezone('EST5EDT'::text, timezone('UTC'::text, time_modified))) <= date(timezone('EST5EDT'::text, timezone('UTC'::text, pe.time_modified))))
49. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..2.18 rows=1 width=29) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Seq Scan on clienthistory ch_1 (cost=0.00..2.17 rows=1 width=29) (actual rows= loops=)

  • Filter: ((name)::text = 'Bytemark Merchant Mobile'::text)
51.          

SubPlan (forNested Loop Left Join)

52. 0.000 0.000 ↓ 0.0

Limit (cost=2.46..2.47 rows=1 width=12) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Sort (cost=2.46..2.47 rows=1 width=12) (actual rows= loops=)

  • Sort Key: i.time_modified DESC
54. 0.000 0.000 ↓ 0.0

Seq Scan on clienthistory i (cost=0.00..2.46 rows=1 width=12) (actual rows= loops=)

  • Filter: ((id = cu_1.client_id) AND ((name)::text = 'Bytemark Merchant Mobile'::text) AND (date(timezone('EST5EDT'::text, timezone('UTC'::text, time_modified))) <= date(timezone('EST5EDT'::text, timezone('UTC'::text, pe.time_modified)))))
55. 0.000 0.000 ↓ 0.0

Index Scan using deviceappinstallationhistory_id_time_modified_idx on deviceappinstallationhistory dai_2 (cost=0.43..41.78 rows=202 width=24) (actual rows= loops=)