explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3gag

Settings
# exclusive inclusive rows x rows loops node
1. 0.022 128,202.758 ↓ 6.0 6 1

Subquery Scan on all_data (cost=402,647.03..402,647.05 rows=1 width=104) (actual time=128,202.743..128,202.758 rows=6 loops=1)

2. 0.000 128,202.736 ↓ 6.0 6 1

Sort (cost=402,647.03..402,647.03 rows=1 width=120) (actual time=128,202.731..128,202.736 rows=6 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
3.          

Initplan (for Sort)

4. 2.004 23.216 ↓ 15.2 792 1

HashAggregate (cost=10,419.77..10,420.29 rows=52 width=4) (actual time=22.673..23.216 rows=792 loops=1)

  • Group Key: edl.entity_id
5.          

CTE user_data_filter

6. 2.470 2.470 ↑ 1.2 2,216 1

Index Scan using idx_user_data_access_user_id on user_data_access (cost=0.43..2,502.35 rows=2,590 width=23) (actual time=0.029..2.470 rows=2,216 loops=1)

  • Index Cond: (user_id = 8,787)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 15
7. 2.238 21.212 ↓ 30.8 1,602 1

Append (cost=0.42..7,917.29 rows=52 width=4) (actual time=0.359..21.212 rows=1,602 loops=1)

8. 6.045 18.818 ↓ 31.4 1,602 1

Nested Loop (cost=0.42..3,065.36 rows=51 width=4) (actual time=0.357..18.818 rows=1,602 loops=1)

9. 6.125 6.125 ↓ 1.7 2,216 1

CTE Scan on user_data_filter da (cost=0.00..51.80 rows=1,295 width=8) (actual time=0.033..6.125 rows=2,216 loops=1)

  • Filter: self_access
10. 6.648 6.648 ↑ 1.0 1 2,216

Index Scan using idx_entity_data_link_entity_type_id_ancestor_type_id_ancestor_i on entity_data_link edl (cost=0.42..2.32 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=2,216)

  • 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
11. 0.001 0.156 ↓ 0.0 0 1

Nested Loop (cost=0.85..4,851.14 rows=1 width=4) (actual time=0.156..0.156 rows=0 loops=1)

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

Nested Loop (cost=0.42..4,789.08 rows=119 width=40) (actual time=0.154..0.155 rows=0 loops=1)

13. 0.153 0.153 ↓ 0.0 0 1

CTE Scan on user_data_filter da_1 (cost=0.00..51.80 rows=1,295 width=40) (actual time=0.152..0.153 rows=0 loops=1)

  • Filter: (NOT self_access)
  • Rows Removed by Filter: 2,216
14. 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..3.65 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))
15. 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.50 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)
16. 197.662 128,202.719 ↓ 6.0 6 1

GroupAggregate (cost=392,226.68..392,226.73 rows=1 width=120) (actual time=127,932.682..128,202.719 rows=6 loops=1)

  • Group Key: (to_timestamp(to_char((elt_1.month_year)::timestamp with time zone, 'YYYYMM'::text), 'YYYYMM'::text)), (' '::text)
17. 362.270 128,005.039 ↓ 192,710.0 192,710 1

Sort (cost=392,226.68..392,226.69 rows=1 width=56) (actual time=127,878.919..128,005.039 rows=192,710 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: 21,200kB
18. 570.105 127,642.769 ↓ 192,710.0 192,710 1

Nested Loop Left Join (cost=358,173.11..392,226.67 rows=1 width=56) (actual time=23,700.965..127,642.769 rows=192,710 loops=1)

19. 597.956 126,687.244 ↓ 192,710.0 192,710 1

Nested Loop (cost=358,172.96..392,226.47 rows=1 width=54) (actual time=23,700.948..126,687.244 rows=192,710 loops=1)

  • Join Filter: (cd.id = elt.entity_id)
20. 347.129 26,764.106 ↓ 5,467.9 191,378 1

Merge Join (cost=358,172.39..391,976.17 rows=35 width=60) (actual time=23,700.927..26,764.106 rows=191,378 loops=1)

  • Merge Cond: (elt_1.entity_id = cd.id)
21. 464.703 26,278.912 ↑ 1.1 191,378 1

Finalize GroupAggregate (cost=357,503.41..388,765.31 rows=203,275 width=79) (actual time=23,594.398..26,278.912 rows=191,378 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
22. 475.288 25,814.209 ↑ 1.0 191,378 1

Gather Merge (cost=357,503.41..382,749.01 rows=196,719 width=47) (actual time=23,594.369..25,814.209 rows=191,378 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
23. 1,073.178 25,338.921 ↑ 1.4 47,844 4 / 4

Partial GroupAggregate (cost=356,503.37..358,634.49 rows=65,573 width=47) (actual time=23,424.650..25,338.921 rows=47,844 loops=4)

  • 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
24. 2,505.882 24,265.743 ↓ 18.2 1,192,976 4 / 4

Sort (cost=356,503.37..356,667.30 rows=65,573 width=47) (actual time=23,424.630..24,265.743 rows=1,192,976 loops=4)

  • 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: external merge Disk: 50,888kB
  • Worker 0: Sort Method: external merge Disk: 46,984kB
  • Worker 1: Sort Method: external merge Disk: 47,488kB
  • Worker 2: Sort Method: external merge Disk: 47,224kB
25. 2,441.584 21,759.861 ↓ 18.2 1,192,976 4 / 4

Nested Loop (cost=1.28..351,257.26 rows=65,573 width=47) (actual time=2.528..21,759.861 rows=1,192,976 loops=4)

26. 23.831 115.945 ↓ 1.4 8,102 4 / 4

Nested Loop (cost=0.71..39,072.73 rows=5,821 width=31) (actual time=2.391..115.945 rows=8,102 loops=4)

27. 59.705 59.705 ↑ 1.2 8,102 4 / 4

Parallel Index Scan Backward using cdno_deleted_ix on child_dno entity (cost=0.42..35,395.83 rows=9,343 width=43) (actual time=2.334..59.705 rows=8,102 loops=4)

  • Index Cond: (client_id = 1,002)
  • Filter: ((ageing IS NOT NULL) AND (status_id = ANY ('{1578,1585,1586,1590,2,1,4}'::integer[])))
  • Rows Removed by Filter: 182
28. 32.409 32.409 ↑ 1.0 1 32,409 / 4

Index Scan using pk_dno on dno (cost=0.29..0.39 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=32,409)

  • Index Cond: (id = entity.dnoid)
  • Filter: (status_id = ANY ('{1572,1573,1574,1575,1580,1581,1582,1583,1588,1589,2,1,5,6}'::integer[]))
29. 19,202.333 19,202.333 ↓ 1.6 147 32,409 / 4

Index Scan using idx_entity_lead_time_6 on entity_lead_time elt_1 (cost=0.57..52.61 rows=94 width=12) (actual time=1.866..2.370 rows=147 loops=32,409)

  • 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: 709
30. 58.777 138.065 ↓ 278.6 32,593 1

Sort (cost=668.99..669.28 rows=117 width=16) (actual time=106.521..138.065 rows=32,593 loops=1)

  • Sort Key: cd.id DESC
  • Sort Method: quicksort Memory: 2,296kB
31. 48.652 79.288 ↓ 278.6 32,597 1

Bitmap Heap Scan on child_dno cd (cost=527.20..664.97 rows=117 width=16) (actual time=32.045..79.288 rows=32,597 loops=1)

  • Recheck Cond: ((dnoid = ANY ($8)) AND (client_id = 1,002))
  • Filter: (ageing IS NOT NULL)
  • Rows Removed by Filter: 864
  • Heap Blocks: exact=10,106
32. 0.344 30.636 ↓ 0.0 0 1

BitmapAnd (cost=527.20..527.20 rows=123 width=0) (actual time=30.636..30.636 rows=0 loops=1)

33. 27.757 27.757 ↓ 13.6 33,461 1

Bitmap Index Scan on idx_child_dno_2 (cost=0.00..44.77 rows=2,469 width=0) (actual time=27.756..27.757 rows=33,461 loops=1)

  • Index Cond: (dnoid = ANY ($8))
34. 2.535 2.535 ↑ 1.0 33,565 1

Bitmap Index Scan on child_dno_client_id_vendor_id_idx (cost=0.00..482.12 rows=34,012 width=0) (actual time=2.535..2.535 rows=33,565 loops=1)

  • Index Cond: (client_id = 1,002)
35. 99,325.182 99,325.182 ↑ 1.0 1 191,378

Index Scan using idx_entity_lead_time_6 on entity_lead_time elt (cost=0.57..7.14 rows=1 width=18) (actual time=0.473..0.519 rows=1 loops=191,378)

  • Index Cond: ((entity_id = elt_1.entity_id) AND (entity_type_id = elt_1.entity_type_id))
  • Filter: ((max(elt_1.month_year)) = month_year)
  • Rows Removed by Filter: 864
36. 385.420 385.420 ↑ 1.0 1 192,710

Index Scan using pk_tier on tier (cost=0.14..0.20 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=192,710)

  • Index Cond: (id = cd.tier_id)
37.          

SubPlan (for GroupAggregate)

38. 0.018 0.018 ↑ 1.0 1 6

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=6)

Planning time : 2.771 ms
Execution time : 128,209.803 ms