explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pHA1

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 273.435 ↓ 5.0 5 1

Subquery Scan on all_data (cost=80,833.55..80,833.57 rows=1 width=80) (actual time=273.431..273.435 rows=5 loops=1)

2. 0.000 273.422 ↓ 5.0 5 1

Sort (cost=80,833.55..80,833.56 rows=1 width=566) (actual time=273.421..273.422 rows=5 loops=1)

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

Initplan (for Sort)

4. 0.004 0.234 ↓ 7.5 15 1

Unique (cost=7,312.46..7,312.47 rows=2 width=4) (actual time=0.230..0.234 rows=15 loops=1)

5.          

CTE user_data_filter

6. 0.036 0.036 ↑ 10.4 59 1

Index Scan using idx_user_data_access_user_id on user_data_access (cost=0.43..1,563.29 rows=612 width=23) (actual time=0.010..0.036 rows=59 loops=1)

  • Index Cond: (user_id = 1,199)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 7
7. 0.011 0.230 ↓ 8.5 17 1

Sort (cost=5,749.17..5,749.17 rows=2 width=4) (actual time=0.229..0.230 rows=17 loops=1)

  • Sort Key: edl.entity_id
  • Sort Method: quicksort Memory: 25kB
8. 0.001 0.219 ↓ 8.5 17 1

Append (cost=0.42..5,749.16 rows=2 width=4) (actual time=0.036..0.219 rows=17 loops=1)

9. 0.032 0.212 ↓ 17.0 17 1

Nested Loop (cost=0.42..2,391.47 rows=1 width=4) (actual time=0.036..0.212 rows=17 loops=1)

10. 0.062 0.062 ↑ 5.2 59 1

CTE Scan on user_data_filter da (cost=0.00..12.24 rows=306 width=8) (actual time=0.013..0.062 rows=59 loops=1)

  • Filter: self_access
11. 0.118 0.118 ↓ 0.0 0 59

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.002..0.002 rows=0 loops=59)

  • 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: 0
12. 0.000 0.006 ↓ 0.0 0 1

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

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

Nested Loop (cost=0.42..3,347.36 rows=16 width=40) (actual time=0.006..0.006 rows=0 loops=1)

14. 0.006 0.006 ↓ 0.0 0 1

CTE Scan on user_data_filter da_1 (cost=0.00..12.24 rows=306 width=40) (actual time=0.006..0.006 rows=0 loops=1)

  • Filter: (NOT self_access)
  • Rows Removed by Filter: 59
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.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))
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.313 273.413 ↓ 5.0 5 1

GroupAggregate (cost=73,521.03..73,521.08 rows=1 width=566) (actual time=273.169..273.413 rows=5 loops=1)

  • Group Key: data.period, data.label
18. 0.228 273.095 ↓ 371.0 371 1

Sort (cost=73,521.03..73,521.03 rows=1 width=566) (actual time=273.067..273.095 rows=371 loops=1)

  • Sort Key: data.period, data.label
  • Sort Method: quicksort Memory: 53kB
19. 0.199 272.867 ↓ 371.0 371 1

Hash Right Join (cost=73,519.34..73,521.02 rows=1 width=566) (actual time=272.710..272.867 rows=371 loops=1)

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

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

21. 0.461 272.630 ↓ 371.0 371 1

Hash (cost=73,519.32..73,519.32 rows=1 width=54) (actual time=272.630..272.630 rows=371 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
22. 1.749 272.169 ↓ 371.0 371 1

Nested Loop (cost=73,492.60..73,519.32 rows=1 width=54) (actual time=4.585..272.169 rows=371 loops=1)

  • Join Filter: (cd.id = elt.entity_id)
23. 0.452 4.413 ↓ 371.0 371 1

Merge Join (cost=73,443.09..73,465.78 rows=1 width=60) (actual time=3.727..4.413 rows=371 loops=1)

  • Merge Cond: (cd.id = data.entity_id)
24. 0.073 0.576 ↓ 46.5 93 1

Sort (cost=62.27..62.28 rows=2 width=16) (actual time=0.536..0.576 rows=93 loops=1)

  • Sort Key: cd.id
  • Sort Method: quicksort Memory: 29kB
25. 0.155 0.503 ↓ 46.5 93 1

Bitmap Heap Scan on child_dno cd (cost=50.70..62.26 rows=2 width=16) (actual time=0.363..0.503 rows=93 loops=1)

  • Recheck Cond: ((client_id = 1,007) AND (dnoid = ANY ($8)))
  • Filter: (ageing IS NOT NULL)
  • Rows Removed by Filter: 16
  • Heap Blocks: exact=75
26. 0.007 0.348 ↓ 0.0 0 1

BitmapAnd (cost=50.70..50.70 rows=3 width=0) (actual time=0.348..0.348 rows=0 loops=1)

27. 0.045 0.045 ↓ 1.3 235 1

Bitmap Index Scan on child_dno_client_id_vendor_id_idx (cost=0.00..5.62 rows=178 width=0) (actual time=0.045..0.045 rows=235 loops=1)

  • Index Cond: (client_id = 1,007)
28. 0.296 0.296 ↑ 1.6 167 1

Bitmap Index Scan on idx_child_dno_2 (cost=0.00..44.83 rows=259 width=0) (actual time=0.296..0.296 rows=167 loops=1)

  • Index Cond: (dnoid = ANY ($8))
29. 0.301 3.385 ↑ 12.2 371 1

Sort (cost=73,380.81..73,392.15 rows=4,535 width=52) (actual time=3.188..3.385 rows=371 loops=1)

  • Sort Key: data.entity_id
  • Sort Method: quicksort Memory: 53kB
30. 0.047 3.084 ↑ 12.2 371 1

Subquery Scan on data (cost=72,912.64..73,105.38 rows=4,535 width=52) (actual time=2.829..3.084 rows=371 loops=1)

31. 0.196 3.037 ↑ 12.2 371 1

GroupAggregate (cost=72,912.64..73,060.03 rows=4,535 width=28) (actual time=2.828..3.037 rows=371 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.255 2.841 ↑ 12.2 371 1

Sort (cost=72,912.64..72,923.98 rows=4,535 width=28) (actual time=2.817..2.841 rows=371 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, entity.dynamic_metadata
  • Sort Method: quicksort Memory: 53kB
33. 0.561 2.586 ↑ 12.2 371 1

Nested Loop (cost=0.72..72,637.21 rows=4,535 width=28) (actual time=0.029..2.586 rows=371 loops=1)

34. 0.444 0.444 ↑ 1.0 93 1

Index Scan Backward using cdno_deleted_ix on child_dno entity (cost=0.29..702.62 rows=94 width=32) (actual time=0.006..0.444 rows=93 loops=1)

  • Index Cond: (client_id = 1,007)
  • Filter: (ageing IS NOT NULL)
  • Rows Removed by Filter: 15
35. 1.581 1.581 ↑ 12.0 4 93

Index Scan using idx_entity_lead_time_6 on entity_lead_time elt_1 (cost=0.44..764.42 rows=48 width=12) (actual time=0.006..0.017 rows=4 loops=93)

  • 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
36. 1.484 266.007 ↑ 1.0 1 371

Bitmap Heap Scan on entity_lead_time elt (cost=49.51..53.53 rows=1 width=18) (actual time=0.716..0.717 rows=1 loops=371)

  • Recheck Cond: ((entity_id = data.entity_id) AND (entity_type_id = data.entity_type_id) AND (month_year = data.month_year))
  • Heap Blocks: exact=371
37. 1.855 264.523 ↓ 0.0 0 371

BitmapAnd (cost=49.51..49.51 rows=1 width=0) (actual time=0.713..0.713 rows=0 loops=371)

38. 2.968 2.968 ↑ 41.7 22 371

Bitmap Index Scan on idx_entity_lead_time_5 (cost=0.00..17.87 rows=918 width=0) (actual time=0.008..0.008 rows=22 loops=371)

  • Index Cond: (entity_id = data.entity_id)
39. 259.700 259.700 ↓ 6.3 9,284 371

Bitmap Index Scan on idx_entity_lead_time_3 (cost=0.00..31.39 rows=1,469 width=0) (actual time=0.700..0.700 rows=9,284 loops=371)

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

SubPlan (for GroupAggregate)

41. 0.005 0.005 ↑ 1.0 1 5

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=5)

Planning time : 2.912 ms
Execution time : 273.845 ms