explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bIz

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 4,077.349 ↑ 8.3 3 1

Limit (cost=619,060.42..619,060.49 rows=25 width=213) (actual time=4,077.343..4,077.349 rows=3 loops=1)

2.          

CTE user_org_ueids

3. 0.490 0.490 ↑ 1.0 48 1

Index Scan using idx_om_user_organization_external_id on om_user_organization ouo_2 (cost=0.43..122.70 rows=48 width=37) (actual time=0.007..0.490 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[]))
4. 0.027 4,077.342 ↑ 16.0 3 1

Sort (cost=618,937.72..618,937.84 rows=48 width=213) (actual time=4,077.340..4,077.342 rows=3 loops=1)

  • Sort Key: ouo.name
  • Sort Method: quicksort Memory: 25kB
5. 0.085 4,077.315 ↑ 16.0 3 1

Hash Left Join (cost=618,812.78..618,936.38 rows=48 width=213) (actual time=4,077.007..4,077.315 rows=3 loops=1)

  • Hash Cond: ((ouo.unique_exchange_id)::text = (COALESCE(t1.ououeid, (unnest(u.user_organization_ueids))))::text)
  • 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 (t1.user_org_packages_infos IS NOT NULL))
  • Rows Removed by Filter: 45
6. 0.308 0.308 ↑ 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.020..0.308 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[]))
7. 0.013 4,076.922 ↑ 50.0 4 1

Hash (cost=618,809.85..618,809.85 rows=200 width=197) (actual time=4,076.922..4,076.922 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
8. 0.026 4,076.909 ↑ 50.0 4 1

Hash Full Join (cost=618,804.59..618,809.85 rows=200 width=197) (actual time=4,076.896..4,076.909 rows=4 loops=1)

  • Hash Cond: (((unnest(u.user_organization_ueids)))::text = (t1.ououeid)::text)
9. 0.076 3,763.004 ↑ 50.0 4 1

HashAggregate (cost=457,261.02..457,263.52 rows=200 width=64) (actual time=3,763.000..3,763.004 rows=4 loops=1)

  • Group Key: (unnest(u.user_organization_ueids))
10. 0.088 3,762.928 ↑ 672,100.0 4 1

HashAggregate (cost=396,570.39..410,214.02 rows=2,688,400 width=96) (actual time=3,762.881..3,762.928 rows=4 loops=1)

  • Group Key: unnest(u.user_organization_ueids), u.subscriber_ueid, op2.start_date, op2.end_date
11. 4.537 3,762.840 ↑ 672,100.0 4 1

Nested Loop (cost=94,695.11..369,686.39 rows=2,688,400 width=96) (actual time=3,755.762..3,762.840 rows=4 loops=1)

  • Join Filter: ((uou.unique_exchange_id)::text = ANY ((u.user_organization_ueids)::text[]))
  • Rows Removed by Join Filter: 5,757
12. 0.047 0.047 ↑ 1.0 48 1

CTE Scan on user_org_ueids uou (cost=0.00..0.96 rows=48 width=90) (actual time=0.005..0.047 rows=48 loops=1)

13. 3.695 3,758.256 ↑ 95.5 120 48

Materialize (cost=94,695.11..342,592.08 rows=11,456 width=96) (actual time=25.662..78.297 rows=120 loops=48)

14. 253.089 3,754.561 ↑ 95.5 120 1

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

15. 684.690 1,992.322 ↓ 1.0 100,610 1

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

  • Merge Cond: (opo2.id = op2.options_package_owner_id)
16. 957.853 957.853 ↓ 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.029..957.853 rows=752,438 loops=1)

17. 192.226 349.779 ↓ 1.0 100,610 1

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

  • Sort Key: op2.options_package_owner_id
  • Sort Method: quicksort Memory: 10,224kB
18. 141.213 157.553 ↓ 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=20.412..157.553 rows=100,610 loops=1)

  • Recheck Cond: ((tariff_ueid)::text = '5734cd43-de6e-4e23-9fe7-bb86398c27a3'::text)
  • Heap Blocks: exact=17,711
19. 16.340 16.340 ↓ 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=16.340..16.340 rows=100,610 loops=1)

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

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

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

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

22. 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)
23. 1,006.100 1,006.100 ↓ 0.0 0 100,610

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

  • Index Cond: ((opo2.user_ueid)::text = (ueid)::text)
24. 0.001 313.879 ↓ 0.0 0 1

Hash (cost=161,543.56..161,543.56 rows=1 width=133) (actual time=313.879..313.879 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
25. 0.003 313.878 ↓ 0.0 0 1

Subquery Scan on t1 (cost=161,543.53..161,543.56 rows=1 width=133) (actual time=313.878..313.878 rows=0 loops=1)

26. 0.004 313.875 ↓ 0.0 0 1

HashAggregate (cost=161,543.53..161,543.55 rows=1 width=129) (actual time=313.875..313.875 rows=0 loops=1)

  • Group Key: ouo_1.unique_exchange_id
27. 0.003 313.871 ↓ 0.0 0 1

Nested Loop (cost=161,084.89..161,543.52 rows=1 width=129) (actual time=313.871..313.871 rows=0 loops=1)

  • Join Filter: ((uou_1.unique_exchange_id)::text = (ouo_1.unique_exchange_id)::text)
28. 7.183 313.868 ↓ 0.0 0 1

Hash Join (cost=161,084.46..161,543.00 rows=1 width=219) (actual time=313.868..313.868 rows=0 loops=1)

  • Hash Cond: (op_in.options_package_owner_id = opo.id)
29. 107.825 304.851 ↑ 1.5 8,197 1

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

  • Group Key: op_in.id
30. 34.088 197.026 ↑ 6.5 18,704 1

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

  • Hash Cond: (o.option_type_id = ot.id)
31. 39.976 162.140 ↑ 6.5 18,704 1

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

32. 29.891 31.997 ↑ 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.352..31.997 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
33. 2.106 2.106 ↑ 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.106..2.106 rows=8,197 loops=1)

  • Index Cond: ((tariff_ueid)::text = ANY ('{b55d396d-e963-4ea9-adb2-04a89de46acf,1d3eb6e0-0b35-40fc-a6d2-b2a2af2dffd8}'::text[]))
34. 90.167 90.167 ↑ 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.008..0.011 rows=2 loops=8,197)

  • Index Cond: (options_package_id = op_in.id)
35. 0.155 0.798 ↓ 1.0 137 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
36. 0.258 0.643 ↓ 1.0 137 1

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

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

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

38. 0.104 0.199 ↑ 1.0 85 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
39. 0.095 0.095 ↑ 1.0 85 1

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

40. 0.086 1.834 ↓ 1.1 44 1

Hash (cost=128.28..128.28 rows=40 width=131) (actual time=1.834..1.834 rows=44 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
41. 0.220 1.748 ↓ 1.1 44 1

Nested Loop (cost=0.42..128.28 rows=40 width=131) (actual time=0.040..1.748 rows=44 loops=1)

42. 0.616 0.616 ↑ 1.0 48 1

CTE Scan on user_org_ueids uou_1 (cost=0.00..0.96 rows=48 width=90) (actual time=0.013..0.616 rows=48 loops=1)

43. 0.912 0.912 ↑ 1.0 1 48

Index Scan using ix_om_options_package_owner_user_organization_ueid on om_options_package_owner opo (cost=0.42..2.64 rows=1 width=41) (actual time=0.018..0.019 rows=1 loops=48)

  • Index Cond: ((user_organization_ueid)::text = (uou_1.unique_exchange_id)::text)
44. 0.000 0.000 ↓ 0.0 0

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) (never executed)

  • Index Cond: (unique_exchange_id = (opo.user_organization_ueid)::text)
  • Heap Fetches: 0
Planning time : 4.145 ms
Execution time : 4,080.119 ms