explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pXcv

Settings
# exclusive inclusive rows x rows loops node
1. 0.029 121.407 ↓ 4.0 4 1

Subquery Scan on all_data (cost=24,963.23..24,963.25 rows=1 width=104) (actual time=121.403..121.407 rows=4 loops=1)

2. 0.000 121.378 ↓ 4.0 4 1

Sort (cost=24,963.23..24,963.23 rows=1 width=120) (actual time=121.377..121.378 rows=4 loops=1)

  • Sort Key: data.period DESC
  • Sort Method: quicksort Memory: 25kB
3.          

Initplan (for Sort)

4. 0.005 0.163 ↓ 6.0 12 1

Unique (cost=7,339.40..7,339.41 rows=2 width=4) (actual time=0.158..0.163 rows=12 loops=1)

5.          

CTE user_data_filter

6. 0.046 0.046 ↑ 87.7 7 1

Index Scan using idx_user_data_access_user_id on user_data_access (cost=0.43..1,572.33 rows=614 width=23) (actual time=0.031..0.046 rows=7 loops=1)

  • Index Cond: (user_id = 1,985)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 4
7. 0.020 0.158 ↓ 6.0 12 1

Sort (cost=5,767.07..5,767.08 rows=2 width=4) (actual time=0.157..0.158 rows=12 loops=1)

  • Sort Key: edl.entity_id
  • Sort Method: quicksort Memory: 25kB
8. 0.004 0.138 ↓ 6.0 12 1

Append (cost=0.42..5,767.06 rows=2 width=4) (actual time=0.061..0.138 rows=12 loops=1)

9. 0.006 0.131 ↓ 12.0 12 1

Nested Loop (cost=0.42..2,399.97 rows=1 width=4) (actual time=0.058..0.131 rows=12 loops=1)

10. 0.055 0.055 ↑ 43.9 7 1

CTE Scan on user_data_filter da (cost=0.00..12.28 rows=307 width=8) (actual time=0.036..0.055 rows=7 loops=1)

  • Filter: self_access
11. 0.070 0.070 ↓ 2.0 2 7

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) (actual time=0.005..0.010 rows=2 loops=7)

  • Index Cond: ((entity_type_id = 12) AND (ancestor_type_id = da.entity_type_id) AND (ancestor_id = da.entity_id))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 3
12. 0.001 0.003 ↓ 0.0 0 1

Nested Loop (cost=0.84..3,367.07 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)

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

Nested Loop (cost=0.42..3,356.11 rows=17 width=40) (actual time=0.002..0.002 rows=0 loops=1)

14. 0.002 0.002 ↓ 0.0 0 1

CTE Scan on user_data_filter da_1 (cost=0.00..12.28 rows=307 width=40) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (NOT self_access)
  • Rows Removed by Filter: 7
15. 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.88 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))
16. 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 = 12) AND (ancestor_type_id = edl_1.entity_type_id) AND (ancestor_id = edl_1.entity_id))
  • Filter: (NOT deleted)
17. 0.199 121.351 ↓ 4.0 4 1

GroupAggregate (cost=17,623.76..17,623.81 rows=1 width=120) (actual time=121.179..121.351 rows=4 loops=1)

  • Group Key: data.period, data.label
18. 0.311 121.148 ↓ 197.0 197 1

Sort (cost=17,623.76..17,623.77 rows=1 width=566) (actual time=121.132..121.148 rows=197 loops=1)

  • Sort Key: data.period, data.label
  • Sort Method: quicksort Memory: 40kB
19. 0.988 120.837 ↓ 197.0 197 1

Nested Loop (cost=17,614.97..17,623.75 rows=1 width=566) (actual time=3.603..120.837 rows=197 loops=1)

  • Join Filter: (cd.id = elt.entity_id)
20. 0.256 3.422 ↓ 197.0 197 1

Merge Join (cost=17,565.74..17,570.49 rows=1 width=572) (actual time=3.067..3.422 rows=197 loops=1)

  • Merge Cond: (cd.id = data.entity_id)
21. 0.042 0.456 ↓ 65.0 65 1

Sort (cost=16.15..16.16 rows=1 width=520) (actual time=0.433..0.456 rows=65 loops=1)

  • Sort Key: cd.id
  • Sort Method: quicksort Memory: 28kB
22. 0.032 0.414 ↓ 65.0 65 1

Hash Right Join (cost=14.46..16.14 rows=1 width=520) (actual time=0.399..0.414 rows=65 loops=1)

  • Hash Cond: (tier.id = cd.tier_id)
23. 0.013 0.013 ↑ 1.0 49 1

Seq Scan on tier (cost=0.00..1.49 rows=49 width=520) (actual time=0.009..0.013 rows=49 loops=1)

24. 0.016 0.369 ↓ 65.0 65 1

Hash (cost=14.45..14.45 rows=1 width=16) (actual time=0.369..0.369 rows=65 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
25. 0.305 0.353 ↓ 65.0 65 1

Bitmap Heap Scan on child_dno cd (cost=10.42..14.45 rows=1 width=16) (actual time=0.240..0.353 rows=65 loops=1)

  • Recheck Cond: ((relation_id = 1,902) AND (dno_type_id = 1,032))
  • Filter: ((ageing IS NOT NULL) AND (client_id = 1,007) AND (dnoid = ANY ($8)))
  • Heap Blocks: exact=57
26. 0.004 0.048 ↓ 0.0 0 1

BitmapAnd (cost=10.42..10.42 rows=1 width=0) (actual time=0.048..0.048 rows=0 loops=1)

27. 0.030 0.030 ↑ 1.0 83 1

Bitmap Index Scan on idx_child_dno_3 (cost=0.00..4.91 rows=83 width=0) (actual time=0.030..0.030 rows=83 loops=1)

  • Index Cond: (relation_id = 1,902)
28. 0.014 0.014 ↑ 1.0 130 1

Bitmap Index Scan on idx_child_dno_6 (cost=0.00..5.26 rows=130 width=0) (actual time=0.014..0.014 rows=130 loops=1)

  • Index Cond: (dno_type_id = 1,032)
29. 0.144 2.710 ↑ 3.5 268 1

Sort (cost=17,549.58..17,551.95 rows=946 width=52) (actual time=2.631..2.710 rows=268 loops=1)

  • Sort Key: data.entity_id
  • Sort Method: quicksort Memory: 45kB
30. 0.037 2.566 ↑ 3.5 268 1

Subquery Scan on data (cost=17,462.62..17,502.82 rows=946 width=52) (actual time=2.404..2.566 rows=268 loops=1)

31. 0.122 2.529 ↑ 3.5 268 1

GroupAggregate (cost=17,462.62..17,493.36 rows=946 width=68) (actual time=2.404..2.529 rows=268 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, entity.dynamic_metadata
32. 0.142 2.407 ↑ 3.5 268 1

Sort (cost=17,462.62..17,464.98 rows=946 width=36) (actual time=2.394..2.407 rows=268 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)), entity.dynamic_metadata
  • Sort Method: quicksort Memory: 45kB
33. 0.309 2.265 ↑ 3.5 268 1

Nested Loop (cost=186.84..17,415.86 rows=946 width=36) (actual time=0.863..2.265 rows=268 loops=1)

34. 0.051 0.960 ↓ 3.8 83 1

Hash Join (cost=186.40..576.10 rows=22 width=20) (actual time=0.830..0.960 rows=83 loops=1)

  • Hash Cond: (entity.dnoid = dno.id)
35. 0.136 0.150 ↓ 1.3 83 1

Bitmap Heap Scan on child_dno entity (cost=5.41..394.94 rows=65 width=32) (actual time=0.057..0.150 rows=83 loops=1)

  • Recheck Cond: (client_id = 1,007)
  • Filter: ((NOT deleted) AND (ageing IS NOT NULL) AND (status_id = ANY ('{2462,2490,2491,2492,2,1,4,21}'::integer[])))
  • Rows Removed by Filter: 65
  • Heap Blocks: exact=78
36. 0.014 0.014 ↑ 1.0 148 1

Bitmap Index Scan on child_dno_client_id_vendor_id_idx (cost=0.00..5.39 rows=148 width=0) (actual time=0.014..0.014 rows=148 loops=1)

  • Index Cond: (client_id = 1,007)
37. 0.099 0.759 ↑ 1.0 691 1

Hash (cost=172.20..172.20 rows=703 width=4) (actual time=0.759..0.759 rows=691 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 33kB
38. 0.660 0.660 ↑ 1.0 691 1

Seq Scan on dno (cost=0.00..172.20 rows=703 width=4) (actual time=0.004..0.660 rows=691 loops=1)

  • Filter: (status_id = ANY ('{2460,2461,2463,2464,2465,2466,2467,2468,2,1,5,6}'::integer[]))
  • Rows Removed by Filter: 1,341
39. 0.996 0.996 ↑ 14.3 3 83

Index Scan using idx_entity_lead_time_6 on entity_lead_time elt_1 (cost=0.44..764.69 rows=43 width=12) (actual time=0.005..0.012 rows=3 loops=83)

  • Index Cond: ((entity_id = entity.id) AND (entity_type_id = 13))
  • Filter: ((ageing IS NOT NULL) AND (month_year < now()) AND (month_year > (to_timestamp(to_char(now(), 'DDMMYYYY'::text), 'DDMMYYYY'::text) - '5 mons'::interval)))
  • Rows Removed by Filter: 1
40. 0.591 116.427 ↑ 1.0 1 197

Bitmap Heap Scan on entity_lead_time elt (cost=49.23..53.25 rows=1 width=18) (actual time=0.591..0.591 rows=1 loops=197)

  • Recheck Cond: ((entity_id = data.entity_id) AND (entity_type_id = data.entity_type_id) AND (month_year = data.month_year))
  • Heap Blocks: exact=197
41. 0.788 115.836 ↓ 0.0 0 197

BitmapAnd (cost=49.23..49.23 rows=1 width=0) (actual time=0.588..0.588 rows=0 loops=197)

42. 1.379 1.379 ↑ 35.2 26 197

Bitmap Index Scan on idx_entity_lead_time_5 (cost=0.00..18.96 rows=914 width=0) (actual time=0.007..0.007 rows=26 loops=197)

  • Index Cond: (entity_id = data.entity_id)
43. 113.669 113.669 ↓ 6.8 9,650 197

Bitmap Index Scan on idx_entity_lead_time_3 (cost=0.00..30.03 rows=1,418 width=0) (actual time=0.577..0.577 rows=9,650 loops=197)

  • Index Cond: ((entity_type_id = data.entity_type_id) AND (month_year = data.month_year))
44.          

SubPlan (for GroupAggregate)

45. 0.004 0.004 ↑ 1.0 1 4

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=4)

Planning time : 5.962 ms
Execution time : 121.864 ms