explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tbRX

Settings
# exclusive inclusive rows x rows loops node
1. 0.297 10.902 ↓ 0.0 0 1

Sort (cost=14,799.91..14,799.92 rows=1 width=80) (actual time=10.902..10.902 rows=0 loops=1)

  • Sort Key: (to_char((to_timestamp(to_char((elt_1.month_year)::timestamp with time zone, 'YYYYMM'::text), 'YYYYMM'::text)), 'Mon-YY'::text))
  • Sort Method: quicksort Memory: 25kB
2. 0.002 10.605 ↓ 0.0 0 1

HashAggregate (cost=14,799.88..14,799.90 rows=1 width=80) (actual time=10.605..10.605 rows=0 loops=1)

  • Group Key: (to_timestamp(to_char((elt_1.month_year)::timestamp with time zone, 'YYYYMM'::text), 'YYYYMM'::text)), ((SubPlan 1))
3. 0.021 10.603 ↓ 0.0 0 1

Sort (cost=14,799.86..14,799.86 rows=1 width=565) (actual time=10.603..10.603 rows=0 loops=1)

  • Sort Key: (to_timestamp(to_char((elt_1.month_year)::timestamp with time zone, 'YYYYMM'::text), 'YYYYMM'::text)) DESC
  • Sort Method: quicksort Memory: 25kB
4.          

Initplan (for Sort)

5. 0.000 0.000 ↓ 0.0 0

Unique (cost=7,312.46..7,312.47 rows=2 width=4) (never executed)

6.          

CTE user_data_filter

7. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_user_data_access_user_id on user_data_access (cost=0.43..1,563.29 rows=612 width=23) (never executed)

  • Index Cond: (user_id = 1,199)
  • Filter: (NOT deleted)
8. 0.000 0.000 ↓ 0.0 0

Sort (cost=5,749.17..5,749.17 rows=2 width=4) (never executed)

  • Sort Key: edl.entity_id
9. 0.000 0.000 ↓ 0.0 0

Append (cost=0.42..5,749.16 rows=2 width=4) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..2,391.47 rows=1 width=4) (never executed)

11. 0.000 0.000 ↓ 0.0 0

CTE Scan on user_data_filter da (cost=0.00..12.24 rows=306 width=8) (never executed)

  • Filter: self_access
12. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_entity_data_link_entity_type_id_ancestor_type_id_ancestor_i on entity_data_link edl (cost=0.42..7.77 rows=1 width=12) (never executed)

  • Index Cond: ((entity_type_id = 63) AND (ancestor_type_id = da.entity_type_id) AND (ancestor_id = da.entity_id))
  • Filter: (NOT deleted)
13. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.84..3,357.67 rows=1 width=4) (never executed)

  • Join Filter: (edc.ancestor_type_id = ANY (da_1.access_type_ids))
14. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..3,347.36 rows=16 width=40) (never executed)

15. 0.000 0.000 ↓ 0.0 0

CTE Scan on user_data_filter da_1 (cost=0.00..12.24 rows=306 width=40) (never executed)

  • Filter: (NOT self_access)
16. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_entity_data_link_ancestor_type_id_ancestor_id on entity_data_link edl_1 (cost=0.42..10.89 rows=1 width=16) (never executed)

  • Index Cond: ((ancestor_type_id = da_1.entity_type_id) AND (ancestor_id = da_1.entity_id))
  • Filter: (parent AND (NOT deleted))
17. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_entity_data_link_entity_type_id_ancestor_type_id_ancestor_i on entity_data_link edc (cost=0.42..0.62 rows=1 width=12) (never executed)

  • Index Cond: ((entity_type_id = 63) AND (ancestor_type_id = edl_1.entity_type_id) AND (ancestor_id = edl_1.entity_id))
  • Filter: (NOT deleted)
18. 0.000 0.000 ↓ 0.0 0

Unique (cost=7,312.46..7,312.47 rows=2 width=4) (never executed)

19.          

CTE user_data_filter

20. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_user_data_access_user_id on user_data_access user_data_access_1 (cost=0.43..1,563.29 rows=612 width=23) (never executed)

  • Index Cond: (user_id = 1,199)
  • Filter: (NOT deleted)
21. 0.000 0.000 ↓ 0.0 0

Sort (cost=5,749.17..5,749.17 rows=2 width=4) (never executed)

  • Sort Key: edl_2.entity_id
22. 0.000 0.000 ↓ 0.0 0

Append (cost=0.42..5,749.16 rows=2 width=4) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..2,391.47 rows=1 width=4) (never executed)

24. 0.000 0.000 ↓ 0.0 0

CTE Scan on user_data_filter da_2 (cost=0.00..12.24 rows=306 width=8) (never executed)

  • Filter: self_access
25. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_entity_data_link_entity_type_id_ancestor_type_id_ancestor_i on entity_data_link edl_2 (cost=0.42..7.77 rows=1 width=12) (never executed)

  • Index Cond: ((entity_type_id = 63) AND (ancestor_type_id = da_2.entity_type_id) AND (ancestor_id = da_2.entity_id))
  • Filter: (NOT deleted)
26. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.84..3,357.67 rows=1 width=4) (never executed)

  • Join Filter: (edc_1.ancestor_type_id = ANY (da_3.access_type_ids))
27. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..3,347.36 rows=16 width=40) (never executed)

28. 0.000 0.000 ↓ 0.0 0

CTE Scan on user_data_filter da_3 (cost=0.00..12.24 rows=306 width=40) (never executed)

  • Filter: (NOT self_access)
29. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_entity_data_link_ancestor_type_id_ancestor_id on entity_data_link edl_3 (cost=0.42..10.89 rows=1 width=16) (never executed)

  • Index Cond: ((ancestor_type_id = da_3.entity_type_id) AND (ancestor_id = da_3.entity_id))
  • Filter: (parent AND (NOT deleted))
30. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_entity_data_link_entity_type_id_ancestor_type_id_ancestor_i on entity_data_link edc_1 (cost=0.42..0.62 rows=1 width=12) (never executed)

  • Index Cond: ((entity_type_id = 63) AND (ancestor_type_id = edl_3.entity_type_id) AND (ancestor_id = edl_3.entity_id))
  • Filter: (NOT deleted)
31. 0.001 10.582 ↓ 0.0 0 1

GroupAggregate (cost=174.87..174.91 rows=1 width=565) (actual time=10.582..10.582 rows=0 loops=1)

  • Group Key: (to_timestamp(to_char((elt_1.month_year)::timestamp with time zone, 'YYYYMM'::text), 'YYYYMM'::text)), (' '::text)
32. 0.013 10.581 ↓ 0.0 0 1

Sort (cost=174.87..174.87 rows=1 width=565) (actual time=10.581..10.581 rows=0 loops=1)

  • Sort Key: (to_timestamp(to_char((elt_1.month_year)::timestamp with time zone, 'YYYYMM'::text), 'YYYYMM'::text)), (' '::text)
  • Sort Method: quicksort Memory: 25kB
33. 0.000 10.568 ↓ 0.0 0 1

Nested Loop Left Join (cost=140.19..174.86 rows=1 width=565) (actual time=10.568..10.568 rows=0 loops=1)

  • Join Filter: (tier.id = cr.tier_id)
34. 0.000 10.568 ↓ 0.0 0 1

Nested Loop (cost=140.19..172.75 rows=1 width=53) (actual time=10.568..10.568 rows=0 loops=1)

35. 0.000 10.568 ↓ 0.0 0 1

Nested Loop (cost=139.91..143.98 rows=1 width=53) (actual time=10.568..10.568 rows=0 loops=1)

36. 0.002 10.568 ↓ 0.0 0 1

GroupAggregate (cost=85.21..85.24 rows=1 width=12) (actual time=10.568..10.568 rows=0 loops=1)

  • Group Key: elt_1.entity_id, (to_timestamp(to_char((elt_1.month_year)::timestamp with time zone, 'YYYYMM'::text), 'YYYYMM'::text)), elt_1.entity_type_id
37. 0.032 10.566 ↓ 0.0 0 1

Sort (cost=85.21..85.22 rows=1 width=12) (actual time=10.566..10.566 rows=0 loops=1)

  • Sort Key: elt_1.entity_id DESC, (to_timestamp(to_char((elt_1.month_year)::timestamp with time zone, 'YYYYMM'::text), 'YYYYMM'::text)), elt_1.entity_type_id
  • Sort Method: quicksort Memory: 25kB
38. 0.005 10.534 ↓ 0.0 0 1

Nested Loop (cost=0.44..85.20 rows=1 width=12) (actual time=10.534..10.534 rows=0 loops=1)

39. 2.015 2.015 ↓ 2.0 2 1

Seq Scan on change_request entity (cost=0.00..69.20 rows=1 width=12) (actual time=1.865..2.015 rows=2 loops=1)

  • Filter: ((NOT deleted) AND (cycle_time IS NOT NULL) AND (client_id = 1,007) AND (status_id = ANY ('{2417,2418,2469,2470,2471,2472,2473,2474,2475,2476,2477,2478,2479,2480,2481,2482,2483,2484,2485,2486,2487,2488,4191,4192,4193,4194,4195,4196,2,1}'::integer[])))
  • Rows Removed by Filter: 702
40. 8.514 8.514 ↓ 0.0 0 2

Index Scan using idx_entity_lead_time_6 on entity_lead_time elt_1 (cost=0.44..15.98 rows=1 width=12) (actual time=4.257..4.257 rows=0 loops=2)

  • Index Cond: ((entity_id = entity.id) AND (entity_type_id = 63))
  • Filter: ((cycle_time IS NOT NULL) AND (month_year < now()) AND (month_year > (to_timestamp(to_char(now(), 'DDMMYYYY'::text), 'DDMMYYYY'::text) - '6 mons'::interval)))
  • Rows Removed by Filter: 82
41. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on entity_lead_time elt (cost=54.70..58.72 rows=1 width=17) (never executed)

  • Recheck Cond: ((entity_id = elt_1.entity_id) AND (entity_type_id = elt_1.entity_type_id) AND (month_year = (max(elt_1.month_year))))
42. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=54.70..54.70 rows=1 width=0) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_entity_lead_time_5 (cost=0.00..19.32 rows=918 width=0) (never executed)

  • Index Cond: (entity_id = elt_1.entity_id)
44. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_entity_lead_time_3 (cost=0.00..35.13 rows=1,469 width=0) (never executed)

  • Index Cond: ((entity_type_id = elt_1.entity_type_id) AND (month_year = (max(elt_1.month_year))))
45. 0.000 0.000 ↓ 0.0 0

Index Scan using change_request_pkey on change_request cr (cost=0.28..28.76 rows=1 width=20) (never executed)

  • Index Cond: ((id = elt.entity_id) AND (id = ANY ($8)) AND (id = ANY ($16)))
  • Filter: ((cycle_time IS NOT NULL) AND (client_id = 1,007))
46. 0.000 0.000 ↓ 0.0 0

Seq Scan on tier (cost=0.00..1.49 rows=49 width=520) (never executed)

47.          

SubPlan (for GroupAggregate)

48. 0.000 0.000 ↓ 0.0 0

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

Planning time : 61.486 ms
Execution time : 12.226 ms