explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DjYI

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

Sort (cost=5,291.84..5,291.85 rows=3 width=1,136) (actual rows= loops=)

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

Subquery Scan on a (cost=5,291.73..5,291.82 rows=3 width=1,136) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

HashAggregate (cost=5,291.73..5,291.76 rows=3 width=434) (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.28..5,291.63 rows=3 width=434) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.28..9.73 rows=1 width=1,096) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

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

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

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

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

Hash Left Join (cost=2,343.68..2,679.92 rows=1 width=103) (actual rows= loops=)

  • Hash Cond: ((cu.client_id = ch.id) AND ((SubPlan 5) = ch.sid))
9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,338.26..2,671.52 rows=1 width=98) (actual rows= loops=)

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

Nested Loop Left Join (cost=2,337.97..2,662.64 rows=1 width=90) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=2,337.69..2,659.29 rows=1 width=90) (actual rows= loops=)

  • Hash Cond: (dai_1.id = pu.device_app_installation_id)
  • Join Filter: (pu.time_modified >= dai_1.time_modified)
12. 0.000 0.000 ↓ 0.0

Seq Scan on deviceappinstallationhistory dai_1 (cost=0.00..297.68 rows=6,368 width=24) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Hash (cost=2,337.68..2,337.68 rows=1 width=90) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Merge Join (cost=2,007.94..2,337.68 rows=1 width=90) (actual rows= loops=)

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

Index Scan using passexceptionhistory_id on passexceptionhistory px (cost=0.28..312.38 rows=4,342 width=20) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Sort (cost=2,006.10..2,006.32 rows=88 width=90) (actual rows= loops=)

  • Sort Key: pu.pass_exception_id
17. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..2,003.26 rows=88 width=90) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Seq Scan on passactivityhistory p_1 (cost=0.00..1,447.76 rows=88 width=66) (actual rows= loops=)

  • Filter: ((type = ANY ('{14000,14002,14003}'::integer[])) AND (date(timezone('EST5EDT'::text, (time_modified)::timestamp with time zone)) >= to_date('19990101'::text, 'YYYYMMDD'::text)) AND (date(timezone('EST5EDT'::text, (time_modified)::timestamp with time zone)) <= to_date('99990101'::text, 'YYYYMMDD'::text)))
19. 0.000 0.000 ↓ 0.0

Index Scan using passuse_pkey on passuse pu (cost=0.29..6.30 rows=1 width=40) (actual rows= loops=)

  • Index Cond: (id = p_1.pass_use_id)
20.          

SubPlan (forMerge Join)

21. 0.000 0.000 ↓ 0.0

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

22. 0.000 0.000 ↓ 0.0

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

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

Index Scan using passexceptionhistory_id on passexceptionhistory x (cost=0.28..8.31 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (id = pu.pass_exception_id)
  • Filter: (date(timezone('EST5EDT'::text, (time_modified)::timestamp with time zone)) <= date(timezone('EST5EDT'::text, (pu.time_modified)::timestamp with time zone)))
24. 0.000 0.000 ↓ 0.0

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

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

Index Scan using idx_clientuserhistory_scd on clientuserhistory cu (cost=0.28..0.53 rows=1 width=28) (actual rows= loops=)

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

SubPlan (forNested Loop Left Join)

27. 0.000 0.000 ↓ 0.0

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

28. 0.000 0.000 ↓ 0.0

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

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

Index Scan using idx_clientuserhistory_scd on clientuserhistory e_1 (cost=0.28..8.31 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (id = a_1.client_user_id)
  • Filter: (date(timezone('EST5EDT'::text, (time_modified)::timestamp with time zone)) <= date(timezone('EST5EDT'::text, (pu.time_modified)::timestamp with time zone)))
30. 0.000 0.000 ↓ 0.0

Hash (cost=5.41..5.41 rows=1 width=33) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Seq Scan on clienthistory ch (cost=0.00..5.41 rows=1 width=33) (actual rows= loops=)

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

SubPlan (forHash Left Join)

33. 0.000 0.000 ↓ 0.0

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

34. 0.000 0.000 ↓ 0.0

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

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

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

  • Filter: ((id = cu.client_id) AND ((name)::text = 'Bytemark Merchant Mobile'::text) AND (date(timezone('EST5EDT'::text, (time_modified)::timestamp with time zone)) <= date(timezone('EST5EDT'::text, (pu.time_modified)::timestamp with time zone))))
36. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,258.00..2,601.95 rows=1 width=103) (actual rows= loops=)

  • Hash Cond: ((cu_1.client_id = ch_1.id) AND ((SubPlan 2) = ch_1.sid))
37. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,252.58..2,593.54 rows=1 width=98) (actual rows= loops=)

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

Nested Loop Left Join (cost=2,252.30..2,584.67 rows=1 width=90) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,252.01..2,581.31 rows=1 width=90) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=2,251.60..2,573.21 rows=1 width=82) (actual rows= loops=)

  • Hash Cond: (dai_2.id = pu_1.device_app_installation_id)
  • Join Filter: (pu_1.time_modified >= dai_2.time_modified)
41. 0.000 0.000 ↓ 0.0

Seq Scan on deviceappinstallationhistory dai_2 (cost=0.00..297.68 rows=6,368 width=24) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Hash (cost=2,251.59..2,251.59 rows=1 width=90) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Hash Join (cost=803.16..2,251.59 rows=1 width=90) (actual rows= loops=)

  • Hash Cond: (p_2.pass_use_id = pu_1.id)
44. 0.000 0.000 ↓ 0.0

Seq Scan on passactivityhistory p_2 (cost=0.00..1,447.76 rows=88 width=66) (actual rows= loops=)

  • Filter: ((type = ANY ('{14000,14002,14003}'::integer[])) AND (date(timezone('EST5EDT'::text, (time_modified)::timestamp with time zone)) >= to_date('19990101'::text, 'YYYYMMDD'::text)) AND (date(timezone('EST5EDT'::text, (time_modified)::timestamp with time zone)) <= to_date('99990101'::text, 'YYYYMMDD'::text)))
45. 0.000 0.000 ↓ 0.0

Hash (cost=802.00..802.00 rows=93 width=40) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Seq Scan on passusehistory pu_1 (cost=0.00..802.00 rows=93 width=40) (actual rows= loops=)

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

Index Scan using idx_passevent_id on passeventhistory pe (cost=0.42..8.10 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)
48. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (p_2.access_token_id = id)
49. 0.000 0.000 ↓ 0.0

Index Scan using idx_clientuserhistory_scd on clientuserhistory cu_1 (cost=0.28..0.53 rows=1 width=28) (actual rows= loops=)

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

SubPlan (forNested Loop Left Join)

51. 0.000 0.000 ↓ 0.0

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

52. 0.000 0.000 ↓ 0.0

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

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

Index Scan using idx_clientuserhistory_scd on clientuserhistory e (cost=0.28..8.31 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (id = a_2.client_user_id)
  • Filter: (date(timezone('EST5EDT'::text, (time_modified)::timestamp with time zone)) <= date(timezone('EST5EDT'::text, (pe.time_modified)::timestamp with time zone)))
54. 0.000 0.000 ↓ 0.0

Hash (cost=5.41..5.41 rows=1 width=33) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

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

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

SubPlan (forHash Left Join)

57. 0.000 0.000 ↓ 0.0

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

58. 0.000 0.000 ↓ 0.0

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

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

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