explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VAhN

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 4,796.026 ↑ 8.3 3 1

Limit (cost=2,817,652.10..2,817,652.17 rows=25 width=213) (actual time=4,796.019..4,796.026 rows=3 loops=1)

2. 0.047 4,796.018 ↑ 16.0 3 1

Sort (cost=2,817,652.10..2,817,652.22 rows=48 width=213) (actual time=4,796.016..4,796.018 rows=3 loops=1)

  • Sort Key: ouo.name
  • Sort Method: quicksort Memory: 25kB
3. 254.408 4,795.971 ↑ 16.0 3 1

Nested Loop Left Join (cost=2,664,060.86..2,817,650.76 rows=48 width=213) (actual time=4,379.451..4,795.971 rows=3 loops=1)

  • Join Filter: ((ouo.unique_exchange_id)::text = (COALESCE(ouo_1.unique_exchange_id, (unnest(u.user_organization_ueids))))::text)
  • Rows Removed by Join Filter: 282,909
  • Filter: (((json_agg(json_build_object('subscriber_ueid', u.subscriber_ueid, 'start_date', op2.start_date, 'end_date', op2.end_date))) IS NOT NULL) OR ((json_agg((json_build_object('tariff_ueid', op_in.tariff_ueid, 'subscription_ueid', op_in.subscription_ueid, 'start_date', op_in.start_date, 'end_date', op_in.end_date))) FILTER (WHERE ((json_build_object('tariff_ueid', op_in.tariff_ueid, 'subscription_ueid', op_in.subscription_ueid, 'start_date', op_in.start_date, 'end_date', op_in.end_date)) IS NOT NULL))) IS NOT NULL))
  • Rows Removed by Filter: 45
4. 1.579 1.579 ↑ 1.0 48 1

Index Scan using idx_om_user_organization_external_id on om_user_organization ouo (cost=0.43..122.70 rows=48 width=85) (actual time=0.015..1.579 rows=48 loops=1)

  • Index Cond: (external_id = ANY ('{1573686,1766924,479056,2095594,1384312,1419676,395332,1974026,1736260,934349,1419684,1284479,1868931,837126,344305,157372,1387617,249791,1397852,2165992,400393,1885641,832316,1456467,928365,393198,209698,1573674,450436,2150781,883242,398415,666089,210894,328966,599831,2157929,2142927,2159200,1663615,1736254,109327,1201276,196748,938893,1689826,2089422,2271510}'::integer[]))
5. 209.095 4,539.984 ↑ 1.8 5,894 48

Materialize (cost=2,664,060.44..2,809,907.50 rows=10,621 width=197) (actual time=88.968..94.583 rows=5,894 loops=48)

6. 12.857 4,330.889 ↑ 1.8 5,894 1

Merge Full Join (cost=2,664,060.44..2,809,854.39 rows=10,621 width=197) (actual time=4,270.415..4,330.889 rows=5,894 loops=1)

  • Merge Cond: (((unnest(u.user_organization_ueids)))::text = (ouo_1.unique_exchange_id)::text)
7. 2.202 3,727.079 ↑ 1.7 119 1

GroupAggregate (cost=2,483,866.53..2,629,073.83 rows=200 width=64) (actual time=3,723.534..3,727.079 rows=119 loops=1)

  • Group Key: (unnest(u.user_organization_ueids))
8. 0.850 3,724.877 ↑ 694.3 165 1

Unique (cost=2,483,866.53..2,627,066.53 rows=114,560 width=96) (actual time=3,723.472..3,724.877 rows=165 loops=1)

9. 2.245 3,724.027 ↑ 16,483.5 695 1

Sort (cost=2,483,866.53..2,512,506.53 rows=11,456,000 width=96) (actual time=3,723.469..3,724.027 rows=695 loops=1)

  • Sort Key: (unnest(u.user_organization_ueids)), u.subscriber_ueid, op2.start_date, op2.end_date
  • Sort Method: quicksort Memory: 122kB
10. 1.737 3,721.782 ↑ 16,483.5 695 1

Nested Loop (cost=94,695.54..452,640.14 rows=11,456,000 width=96) (actual time=1,103.689..3,721.782 rows=695 loops=1)

11. 227.241 3,717.405 ↑ 95.5 120 1

Nested Loop (cost=94,695.11..342,534.80 rows=11,456 width=96) (actual time=1,103.616..3,717.405 rows=120 loops=1)

12. 678.469 1,880.404 ↓ 1.0 100,610 1

Merge Join (cost=94,694.22..138,373.39 rows=100,150 width=68) (actual time=1,103.057..1,880.404 rows=100,610 loops=1)

  • Merge Cond: (opo2.id = op2.options_package_owner_id)
13. 869.209 869.209 ↓ 1.0 752,438 1

Index Scan using om_options_package_owner_pkey on om_options_package_owner opo2 (cost=0.42..40,296.28 rows=752,425 width=56) (actual time=0.006..869.209 rows=752,438 loops=1)

14. 191.652 332.726 ↓ 1.0 100,610 1

Sort (cost=94,693.80..94,944.24 rows=100,176 width=20) (actual time=240.101..332.726 rows=100,610 loops=1)

  • Sort Key: op2.options_package_owner_id
  • Sort Method: quicksort Memory: 10,224kB
15. 126.242 141.074 ↓ 1.0 100,610 1

Bitmap Heap Scan on om_options_package op2 (cost=1,692.12..86,373.09 rows=100,176 width=20) (actual time=18.646..141.074 rows=100,610 loops=1)

  • Recheck Cond: ((tariff_ueid)::text = '5734cd43-de6e-4e23-9fe7-bb86398c27a3'::text)
  • Heap Blocks: exact=17,711
16. 14.832 14.832 ↓ 1.0 100,610 1

Bitmap Index Scan on ix_om_options_package_tariff_end_date_index (cost=0.00..1,667.08 rows=100,176 width=0) (actual time=14.832..14.832 rows=100,610 loops=1)

  • Index Cond: ((tariff_ueid)::text = '5734cd43-de6e-4e23-9fe7-bb86398c27a3'::text)
17. 201.220 1,609.760 ↓ 0.0 0 100,610

Bitmap Heap Scan on om_user u (cost=0.89..2.02 rows=2 width=115) (actual time=0.016..0.016 rows=0 loops=100,610)

  • Recheck Cond: ((opo2.subscriber_ueid = subscriber_ueid) OR ((opo2.user_ueid)::text = (ueid)::text))
  • Heap Blocks: exact=120
18. 201.220 1,408.540 ↓ 0.0 0 100,610

BitmapOr (cost=0.89..0.89 rows=2 width=0) (actual time=0.014..0.014 rows=0 loops=100,610)

19. 100.610 100.610 ↓ 0.0 0 100,610

Bitmap Index Scan on ix_om_user_subscriber_ueid (cost=0.00..0.44 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=100,610)

  • Index Cond: (opo2.subscriber_ueid = subscriber_ueid)
20. 1,106.710 1,106.710 ↓ 0.0 0 100,610

Bitmap Index Scan on om_user_pkey (cost=0.00..0.44 rows=1 width=0) (actual time=0.011..0.011 rows=0 loops=100,610)

  • Index Cond: ((opo2.user_ueid)::text = (ueid)::text)
21. 2.640 2.640 ↑ 10.0 1 120

Index Only Scan using om_user_organization_unique_exchange_id_key on om_user_organization uou (cost=0.43..4.54 rows=10 width=37) (actual time=0.013..0.022 rows=1 loops=120)

  • Index Cond: (unique_exchange_id = ANY ((u.user_organization_ueids)::text[]))
  • Heap Fetches: 163
22. 11.342 590.953 ↑ 1.8 5,787 1

Materialize (cost=180,193.91..180,645.30 rows=10,621 width=133) (actual time=546.871..590.953 rows=5,787 loops=1)

23. 25.957 579.611 ↑ 1.8 5,787 1

GroupAggregate (cost=180,193.91..180,512.54 rows=10,621 width=129) (actual time=546.862..579.611 rows=5,787 loops=1)

  • Group Key: ouo_1.unique_exchange_id
24. 43.280 553.654 ↑ 1.4 7,570 1

Sort (cost=180,193.91..180,220.46 rows=10,621 width=129) (actual time=546.802..553.654 rows=7,570 loops=1)

  • Sort Key: ouo_1.unique_exchange_id
  • Sort Method: quicksort Memory: 2,706kB
25. 23.444 510.374 ↑ 1.4 7,570 1

Nested Loop (cost=160,956.54..179,483.65 rows=10,621 width=129) (actual time=198.536..510.374 rows=7,570 loops=1)

26. 28.566 372.000 ↑ 1.7 7,662 1

Nested Loop (cost=160,956.11..172,898.45 rows=12,649 width=129) (actual time=198.509..372.000 rows=7,662 loops=1)

27. 120.929 286.055 ↑ 1.5 8,197 1

HashAggregate (cost=160,955.68..161,240.29 rows=12,649 width=130) (actual time=198.489..286.055 rows=8,197 loops=1)

  • Group Key: op_in.id
28. 31.510 165.126 ↑ 6.5 18,704 1

Hash Join (cost=239.05..159,125.95 rows=121,982 width=130) (actual time=4.326..165.126 rows=18,704 loops=1)

  • Hash Cond: (o.option_type_id = ot.id)
29. 40.970 132.764 ↑ 6.5 18,704 1

Nested Loop (cost=216.32..157,425.97 rows=121,982 width=107) (actual time=3.450..132.764 rows=18,704 loops=1)

30. 24.159 26.218 ↑ 1.5 8,197 1

Bitmap Heap Scan on om_options_package op_in (cost=215.75..13,568.94 rows=12,649 width=98) (actual time=3.425..26.218 rows=8,197 loops=1)

  • Recheck Cond: ((tariff_ueid)::text = ANY ('{b55d396d-e963-4ea9-adb2-04a89de46acf,1d3eb6e0-0b35-40fc-a6d2-b2a2af2dffd8}'::text[]))
  • Heap Blocks: exact=6,777
31. 2.059 2.059 ↑ 1.5 8,197 1

Bitmap Index Scan on ix_om_options_package_tariff_end_date_index (cost=0.00..212.59 rows=12,649 width=0) (actual time=2.059..2.059 rows=8,197 loops=1)

  • Index Cond: ((tariff_ueid)::text = ANY ('{b55d396d-e963-4ea9-adb2-04a89de46acf,1d3eb6e0-0b35-40fc-a6d2-b2a2af2dffd8}'::text[]))
32. 65.576 65.576 ↑ 12.5 2 8,197

Index Scan using ix_option_fk_option_options_package_id on om_option o (cost=0.56..11.12 rows=25 width=13) (actual time=0.006..0.008 rows=2 loops=8,197)

  • Index Cond: (options_package_id = op_in.id)
33. 0.216 0.852 ↓ 1.0 137 1

Hash (cost=21.07..21.07 rows=133 width=31) (actual time=0.852..0.852 rows=137 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
34. 0.289 0.636 ↓ 1.0 137 1

Hash Join (cost=7.91..21.07 rows=133 width=31) (actual time=0.171..0.636 rows=137 loops=1)

  • Hash Cond: (ot.option_type_nick_id = otn.id)
35. 0.199 0.199 ↓ 1.0 137 1

Seq Scan on om_option_type ot (cost=0.00..11.33 rows=133 width=14) (actual time=0.010..0.199 rows=137 loops=1)

36. 0.073 0.148 ↑ 1.0 85 1

Hash (cost=6.85..6.85 rows=85 width=25) (actual time=0.148..0.148 rows=85 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
37. 0.075 0.075 ↑ 1.0 85 1

Seq Scan on om_option_type_nick otn (cost=0.00..6.85 rows=85 width=25) (actual time=0.003..0.075 rows=85 loops=1)

38. 57.379 57.379 ↑ 1.0 1 8,197

Index Only Scan using _ix_spark_report_speed_up on om_options_package_owner opo (cost=0.42..0.90 rows=1 width=41) (actual time=0.006..0.007 rows=1 loops=8,197)

  • Index Cond: (id = op_in.options_package_owner_id)
  • Heap Fetches: 26
39. 114.930 114.930 ↑ 1.0 1 7,662

Index Only Scan using om_user_organization_unique_exchange_id_key on om_user_organization ouo_1 (cost=0.43..0.51 rows=1 width=37) (actual time=0.014..0.015 rows=1 loops=7,662)

  • Index Cond: (unique_exchange_id = (opo.user_organization_ueid)::text)
  • Heap Fetches: 7,570
Planning time : 2.805 ms
Execution time : 4,796.551 ms