explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aG3B

Settings
# exclusive inclusive rows x rows loops node
1. 2.004 4,799.336 ↓ 318.6 4,142 1

Subquery Scan on all_data (cost=76,519.17..76,544.29 rows=13 width=242) (actual time=3,493.987..4,799.336 rows=4,142 loops=1)

2.          

CTE domain_user_with_flowdown_rgs

3. 8.011 3,411.735 ↓ 199.7 4,394 1

HashAggregate (cost=76,511.70..76,511.92 rows=22 width=1,056) (actual time=3,409.541..3,411.735 rows=4,394 loops=1)

  • Group Key: du_1.entity_id, du_1.role_group_id, du_1.user_group_id, du_1.user_id, rg.client_id, rg.entity_type_id, u.first_name, u.last_name
4. 1.736 3,403.724 ↓ 199.7 4,394 1

Append (cost=75.48..76,511.26 rows=22 width=1,056) (actual time=10.735..3,403.724 rows=4,394 loops=1)

5. 7.520 2,467.234 ↓ 295.2 4,133 1

Nested Loop Left Join (cost=75.48..44,752.14 rows=14 width=37) (actual time=10.734..2,467.234 rows=4,133 loops=1)

6. 11.432 719.721 ↓ 295.2 4,133 1

Nested Loop (cost=75.20..44,747.68 rows=14 width=24) (actual time=6.427..719.721 rows=4,133 loops=1)

7. 11.581 51.709 ↓ 4.3 32,829 1

Nested Loop (cost=74.91..30,782.30 rows=7,555 width=24) (actual time=2.591..51.709 rows=32,829 loops=1)

8. 0.188 0.188 ↑ 1.5 2 1

Index Scan using pk_role_group on role_group rg (cost=0.14..17.37 rows=3 width=12) (actual time=0.036..0.188 rows=2 loops=1)

  • Filter: ((entity_type_id = 61) AND (client_id = 1,002))
  • Rows Removed by Filter: 226
9. 31.330 39.940 ↓ 3.9 16,414 2

Bitmap Heap Scan on domain_user du_1 (cost=74.77..10,212.49 rows=4,249 width=16) (actual time=1.279..19.970 rows=16,414 loops=2)

  • Recheck Cond: (role_group_id = rg.id)
  • Filter: ((user_id = 1,044) OR (SubPlan 1))
  • Rows Removed by Filter: 1,622
  • Heap Blocks: exact=2,873
10. 2.124 2.124 ↓ 2.6 18,062 2

Bitmap Index Scan on idx_domain_user_3 (cost=0.00..73.70 rows=6,984 width=0) (actual time=1.062..1.062 rows=18,062 loops=2)

  • Index Cond: (role_group_id = rg.id)
11.          

SubPlan (for Bitmap Heap Scan)

12. 3.243 6.486 ↓ 0.0 0 3,243

ProjectSet (cost=0.00..1.61 rows=100 width=4) (actual time=0.002..0.002 rows=0 loops=3,243)

13. 3.243 3.243 ↓ 0.0 0 3,243

Seq Scan on user_group ug (cost=0.00..1.10 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=3,243)

  • Filter: (id = du_1.user_group_id)
  • Rows Removed by Filter: 8
14. 656.580 656.580 ↓ 0.0 0 32,829

Index Scan using pk_contract on contract c_1 (cost=0.29..1.85 rows=1 width=4) (actual time=0.020..0.020 rows=0 loops=32,829)

  • Index Cond: (id = du_1.entity_id)
  • Filter: ((NOT deleted) AND ((((change_tz(exp_date, 'UTC'::character varying, 'IST'::character varying))::date >= '2020-07-24 00:00:00'::timestamp without time zone) AND ((change_tz(exp_date, 'UTC'::character varying, 'IST'::character varying))::date <= '2020-09-15 00:00:00'::timestamp without time zone)) OR (((change_tz(notice_lead_date, 'UTC'::character varying, 'IST'::character varying))::date >= '2020-07-24 00:00:00'::timestamp without time zone) AND ((change_tz(notice_lead_date, 'UTC'::character varying, 'IST'::character varying))::date <= '2020-09-15 00:00:00'::timestamp without time zone)) OR (((change_tz(notice_date, 'UTC'::character varying, 'IST'::character varying))::date >= '2020-07-24 00:00:00'::timestamp without time zone) AND ((change_tz(notice_date, 'UTC'::character varying, 'IST'::character varying))::date <= '2020-09-15 00:00:00'::timestamp without time zone))))
  • Rows Removed by Filter: 1
15. 1,739.993 1,739.993 ↑ 1.0 1 4,133

Index Scan using pk_app_user on app_user u (cost=0.28..0.32 rows=1 width=17) (actual time=0.421..0.421 rows=1 loops=4,133)

  • Index Cond: (du_1.user_id = id)
16. 0.405 934.754 ↓ 32.6 261 1

Nested Loop Left Join (cost=7.19..31,758.78 rows=8 width=37) (actual time=3.040..934.754 rows=261 loops=1)

17. 0.290 826.817 ↓ 32.6 261 1

Merge Join (cost=6.91..31,756.23 rows=8 width=24) (actual time=2.573..826.817 rows=261 loops=1)

  • Merge Cond: (prg.id = crg.flowdown_rolegroup)
18. 24.914 826.407 ↑ 22.0 261 1

Nested Loop (cost=0.57..4,336,029.80 rows=5,736 width=20) (actual time=2.529..826.407 rows=261 loops=1)

  • Join Filter: (du_2.role_group_id = prg.id)
  • Rows Removed by Join Filter: 148,121
19. 0.134 0.134 ↓ 1.1 13 1

Index Scan using pk_role_group on role_group prg (cost=0.14..16.83 rows=12 width=4) (actual time=0.010..0.134 rows=13 loops=1)

  • Filter: (entity_type_id = 1)
  • Rows Removed by Filter: 215
20. 23.839 801.359 ↑ 9.0 11,414 13

Materialize (cost=0.42..4,317,685.91 rows=103,251 width=16) (actual time=0.194..61.643 rows=11,414 loops=13)

21. 7.566 777.520 ↑ 9.0 11,414 1

Nested Loop (cost=0.42..4,317,169.66 rows=103,251 width=16) (actual time=2.513..777.520 rows=11,414 loops=1)

22. 525.685 583.564 ↑ 9.6 4,142 1

Subquery Scan on et (cost=0.00..4,133,234.12 rows=39,874 width=8) (actual time=2.480..583.564 rows=4,142 loops=1)

  • Filter: ((((change_tz(et.exp_date, 'UTC'::character varying, 'IST'::character varying))::date >= '2020-07-24 00:00:00'::timestamp without time zone) AND ((change_tz(et.exp_date, 'UTC'::character varying, 'IST'::character varying))::date <= '2020-09-15 00:00:00'::timestamp without time zone)) OR (((change_tz(et.notice_lead_date, 'UTC'::character varying, 'IST'::character varying))::date >= '2020-07-24 00:00:00'::timestamp without time zone) AND ((change_tz(et.notice_lead_date, 'UTC'::character varying, 'IST'::character varying))::date <= '2020-09-15 00:00:00'::timestamp without time zone)) OR (((change_tz(et.notice_date, 'UTC'::character varying, 'IST'::character varying))::date >= '2020-07-24 00:00:00'::timestamp without time zone) AND ((change_tz(et.notice_date, 'UTC'::character varying, 'IST'::character varying))::date <= '2020-09-15 00:00:00'::timestamp without time zone)))
  • Rows Removed by Filter: 23,433
23. 31.284 57.879 ↑ 96.9 27,575 1

ProjectSet (cost=0.00..18,970.12 rows=2,671,600 width=32) (actual time=0.013..57.879 rows=27,575 loops=1)

24. 26.595 26.595 ↑ 1.0 26,698 1

Seq Scan on contract (cost=0.00..5,411.75 rows=26,716 width=53) (actual time=0.006..26.595 rows=26,698 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 7,677
25. 103.311 186.390 ↑ 1.0 3 4,142

Index Scan using idx_domain_user_entity_id_role_group_id on domain_user du_2 (cost=0.42..4.58 rows=3 width=16) (actual time=0.013..0.045 rows=3 loops=4,142)

  • Index Cond: (entity_id = et.relation_id)
  • Filter: ((user_id = 1,044) OR (SubPlan 2))
  • Rows Removed by Filter: 20
26.          

SubPlan (for Index Scan)

27. 0.000 83.079 ↓ 0.0 0 83,079

ProjectSet (cost=0.00..1.61 rows=100 width=4) (actual time=0.001..0.001 rows=0 loops=83,079)

28. 83.079 83.079 ↓ 0.0 0 83,079

Seq Scan on user_group ug_1 (cost=0.00..1.10 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=83,079)

  • Filter: (id = du_2.user_group_id)
  • Rows Removed by Filter: 8
29. 0.088 0.120 ↓ 87.3 262 1

Sort (cost=6.34..6.35 rows=3 width=16) (actual time=0.039..0.120 rows=262 loops=1)

  • Sort Key: crg.flowdown_rolegroup
  • Sort Method: quicksort Memory: 25kB
30. 0.009 0.032 ↑ 1.5 2 1

Bitmap Heap Scan on role_group crg (cost=3.14..6.32 rows=3 width=16) (actual time=0.027..0.032 rows=2 loops=1)

  • Recheck Cond: ((entity_type_id = 61) AND (client_id = 1,002))
  • Heap Blocks: exact=5
31. 0.002 0.023 ↓ 0.0 0 1

BitmapAnd (cost=3.14..3.14 rows=3 width=0) (actual time=0.023..0.023 rows=0 loops=1)

32. 0.012 0.012 ↓ 1.2 20 1

Bitmap Index Scan on role_group_entity_type_id_idx (cost=0.00..1.37 rows=16 width=0) (actual time=0.011..0.012 rows=20 loops=1)

  • Index Cond: (entity_type_id = 61)
33. 0.009 0.009 ↓ 1.8 66 1

Bitmap Index Scan on idx_role_grp_client_id (cost=0.00..1.52 rows=37 width=0) (actual time=0.009..0.009 rows=66 loops=1)

  • Index Cond: (client_id = 1,002)
34. 107.532 107.532 ↑ 1.0 1 261

Index Scan using pk_app_user on app_user u_1 (cost=0.28..0.32 rows=1 width=17) (actual time=0.412..0.412 rows=1 loops=261)

  • Index Cond: (du_2.user_id = id)
35. 58.150 4,797.332 ↓ 318.6 4,142 1

HashAggregate (cost=7.25..32.25 rows=13 width=267) (actual time=3,493.986..4,797.332 rows=4,142 loops=1)

  • Group Key: c.id, r.name, ets.description
36. 4.030 3,488.298 ↓ 635.1 8,256 1

Nested Loop Left Join (cost=1.27..7.16 rows=13 width=143) (actual time=3,409.677..3,488.298 rows=8,256 loops=1)

37. 7.638 3,467.774 ↓ 8,247.0 8,247 1

Nested Loop (cost=0.99..4.04 rows=1 width=117) (actual time=3,409.651..3,467.774 rows=8,247 loops=1)

38. 4.665 3,451.889 ↓ 8,247.0 8,247 1

Nested Loop (cost=0.71..3.74 rows=1 width=99) (actual time=3,409.631..3,451.889 rows=8,247 loops=1)

39. 3.593 3,430.692 ↓ 4,133.0 4,133 1

Nested Loop (cost=0.29..3.03 rows=1 width=103) (actual time=3,409.564..3,430.692 rows=4,133 loops=1)

40. 3,414.700 3,414.700 ↓ 4,133.0 4,133 1

CTE Scan on domain_user_with_flowdown_rgs du (cost=0.00..0.49 rows=1 width=4) (actual time=3,409.544..3,414.700 rows=4,133 loops=1)

  • Filter: (role_group_id = 2,001)
  • Rows Removed by Filter: 261
41. 12.399 12.399 ↑ 1.0 1 4,133

Index Scan using pk_contract on contract c (cost=0.29..2.54 rows=1 width=99) (actual time=0.003..0.003 rows=1 loops=4,133)

  • Index Cond: (id = du.entity_id)
  • Filter: ((NOT deleted) AND (client_id = 1,002) AND (status_id = ANY ('{1610,1608,1606,1604,1607,1603,1605,1609,5,1515,1,1474,1473,1471,6,1517,1470,2,1472,1516}'::integer[])))
42. 16.532 16.532 ↑ 1.0 2 4,133

Index Only Scan using idx_link_entity_function_6 on link_entity_function lef (cost=0.42..0.69 rows=2 width=4) (actual time=0.003..0.004 rows=2 loops=4,133)

  • Index Cond: ((entity_id = c.id) AND (entity_type_id = 61))
  • Heap Fetches: 8,281
43. 8.247 8.247 ↑ 1.0 1 8,247

Index Scan using pk_work_flow_status on work_flow_status ets (cost=0.28..0.30 rows=1 width=22) (actual time=0.001..0.001 rows=1 loops=8,247)

  • Index Cond: (id = c.status_id)
44. 16.494 16.494 ↑ 10.0 1 8,247

Index Scan using pk_relation on relation r (cost=0.28..3.02 rows=10 width=55) (actual time=0.001..0.002 rows=1 loops=8,247)

  • Index Cond: (id = ANY (c.relation_ids))
45.          

SubPlan (for HashAggregate)

46. 12.426 1,250.884 ↑ 1.0 1 4,142

Aggregate (cost=1.64..1.65 rows=1 width=32) (actual time=0.302..0.302 rows=1 loops=4,142)

47. 24.852 1,238.458 ↑ 1.0 1 4,142

Hash Right Join (cost=0.51..1.63 rows=1 width=1,310) (actual time=0.297..0.299 rows=1 loops=4,142)

  • Hash Cond: (ug_2.id = du_3.user_group_id)
48. 12.426 12.426 ↑ 1.0 8 4,142

Seq Scan on user_group ug_2 (cost=0.00..1.08 rows=8 width=278) (actual time=0.001..0.003 rows=8 loops=4,142)

49. 4.142 1,201.180 ↑ 1.0 1 4,142

Hash (cost=0.49..0.49 rows=1 width=1,040) (actual time=0.290..0.290 rows=1 loops=4,142)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
50. 1,197.038 1,197.038 ↑ 1.0 1 4,142

CTE Scan on domain_user_with_flowdown_rgs du_3 (cost=0.00..0.49 rows=1 width=1,040) (actual time=0.142..0.289 rows=1 loops=4,142)

  • Filter: (entity_id = c.id)
  • Rows Removed by Filter: 4,393
Planning time : 8.571 ms
Execution time : 4,802.122 ms