explain.depesz.com

PostgreSQL's explain analyze made readable

Result: q0W

Settings
# exclusive inclusive rows x rows loops node
1. 0.044 2,223.461 ↑ 53.1 25 1

Hash Right Join (cost=175,503.91..227,135.01 rows=1,328 width=217) (actual time=2,223.434..2,223.461 rows=25 loops=1)

  • Hash Cond: ((user_org_opo.user_organization_ueid)::text = (t1.vueid)::text)
2. 0.002 1,841.565 ↓ 0.0 0 1

GroupAggregate (cost=256.20..51,887.05 rows=1 width=69) (actual time=1,841.565..1,841.565 rows=0 loops=1)

  • Group Key: user_org_opo.user_organization_ueid
3. 0.003 1,841.563 ↓ 0.0 0 1

Nested Loop (cost=256.20..51,887.03 rows=1 width=69) (actual time=1,841.563..1,841.563 rows=0 loops=1)

4. 0.628 1,841.560 ↓ 0.0 0 1

Nested Loop (cost=255.77..51,886.02 rows=1 width=57) (actual time=1,841.560..1,841.560 rows=0 loops=1)

  • Join Filter: ((user_org_opo.user_organization_ueid)::text = ANY ((u.user_organization_ueids)::text[]))
  • Rows Removed by Join Filter: 106
5. 0.029 0.029 ↑ 1.0 1 1

Index Only Scan using ix_om_options_package_owner_user_organization_ueid on om_options_package_owner user_org_opo (cost=0.42..2.64 rows=1 width=37) (actual time=0.026..0.029 rows=1 loops=1)

  • Index Cond: ((user_organization_ueid IS NOT NULL) AND (user_organization_ueid = '95dd0062-3349-4741-9ce0-db883f53f475'::text))
  • Heap Fetches: 0
6. 498.363 1,840.903 ↑ 87.8 106 1

Merge Join (cost=255.35..51,674.08 rows=9,302 width=84) (actual time=38.140..1,840.903 rows=106 loops=1)

  • Merge Cond: (u.subscriber_ueid = subscriber_opo.subscriber_ueid)
7. 1,310.095 1,310.095 ↑ 1.0 572,983 1

Index Scan using ix_om_user_subscriber_ueid on om_user u (cost=0.42..49,469.63 rows=573,028 width=80) (actual time=0.013..1,310.095 rows=572,983 loops=1)

8. 32.445 32.445 ↑ 65.7 11,458 1

Index Scan using ix_om_options_package_owner_subscriber_ueid on om_options_package_owner subscriber_opo (cost=0.42..39,913.10 rows=752,425 width=20) (actual time=0.020..32.445 rows=11,458 loops=1)

9. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_om_options_package_fk_om_options_package_owner on om_options_package subscriber_op (cost=0.43..1.00 rows=1 width=20) (never executed)

  • Index Cond: (options_package_owner_id = subscriber_opo.id)
  • Filter: ((tariff_ueid)::text = '5734cd43-de6e-4e23-9fe7-bb86398c27a3'::text)
10. 0.048 381.852 ↑ 53.1 25 1

Hash (cost=175,231.11..175,231.11 rows=1,328 width=185) (actual time=381.852..381.852 rows=25 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 26kB
11. 0.045 381.804 ↑ 53.1 25 1

Subquery Scan on t1 (cost=175,164.71..175,231.11 rows=1,328 width=185) (actual time=381.254..381.804 rows=25 loops=1)

12. 0.561 381.759 ↑ 53.1 25 1

GroupAggregate (cost=175,164.71..175,217.83 rows=1,328 width=286) (actual time=381.250..381.759 rows=25 loops=1)

  • Group Key: opo.name, opo.user_organization_ueid, opo.subscriber_ueid, opo.user_ueid
13. 0.347 381.198 ↑ 28.3 47 1

Sort (cost=175,164.71..175,168.03 rows=1,328 width=286) (actual time=381.149..381.198 rows=47 loops=1)

  • Sort Key: opo.name, opo.user_organization_ueid, opo.subscriber_ueid, opo.user_ueid
  • Sort Method: quicksort Memory: 33kB
14. 17.363 380.851 ↑ 28.3 47 1

Nested Loop (cost=147,953.43..175,095.82 rows=1,328 width=286) (actual time=213.855..380.851 rows=47 loops=1)

15. 23.230 306.109 ↑ 1.5 8,197 1

Nested Loop (cost=147,953.00..168,836.98 rows=12,649 width=154) (actual time=203.220..306.109 rows=8,197 loops=1)

16. 49.344 217.303 ↑ 1.5 8,197 1

HashAggregate (cost=147,952.44..148,205.42 rows=12,649 width=36) (actual time=203.202..217.303 rows=8,197 loops=1)

  • Group Key: op_in.id
17. 34.729 167.959 ↑ 6.5 18,704 1

Hash Join (cost=129.52..146,122.71 rows=121,982 width=36) (actual time=0.775..167.959 rows=18,704 loops=1)

  • Hash Cond: (o.option_type_id = ot.id)
18. 40.503 132.515 ↑ 6.5 18,704 1

Nested Loop (cost=1.12..144,317.06 rows=121,982 width=13) (actual time=0.044..132.515 rows=18,704 loops=1)

19. 10.042 10.042 ↑ 1.5 8,197 1

Index Only Scan using ix_om_options_package_tariff_ueid_id on om_options_package op_in (cost=0.56..460.03 rows=12,649 width=4) (actual time=0.024..10.042 rows=8,197 loops=1)

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

  • Index Cond: (options_package_id = op_in.id)
21. 0.123 0.715 ↑ 1.0 133 1

Hash (cost=126.74..126.74 rows=133 width=31) (actual time=0.715..0.715 rows=133 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
22. 0.313 0.592 ↑ 1.0 133 1

Merge Join (cost=0.29..126.74 rows=133 width=31) (actual time=0.019..0.592 rows=133 loops=1)

  • Merge Cond: (otn.id = ot.option_type_nick_id)
23. 0.088 0.088 ↑ 1.0 84 1

Index Scan using option_type_nick_pkey on om_option_type_nick otn (cost=0.14..32.21 rows=85 width=25) (actual time=0.006..0.088 rows=84 loops=1)

24. 0.191 0.191 ↑ 1.0 133 1

Index Scan using ix_om_option_type_fk_option_type_option_type_nick_id on om_option_type ot (cost=0.14..93.42 rows=133 width=14) (actual time=0.007..0.191 rows=133 loops=1)

25. 65.576 65.576 ↑ 1.0 1 8,197

Index Only Scan using ix_om_options_package_spark_admin_speed_up on om_options_package op (cost=0.56..1.61 rows=1 width=98) (actual time=0.007..0.008 rows=1 loops=8,197)

  • Index Cond: (id = op_in.id)
  • Heap Fetches: 51
26. 57.379 57.379 ↓ 0.0 0 8,197

Index Scan using om_options_package_owner_pkey on om_options_package_owner opo (cost=0.42..0.48 rows=1 width=140) (actual time=0.007..0.007 rows=0 loops=8,197)

  • Index Cond: (id = op.options_package_owner_id)
  • Filter: (((user_organization_ueid)::text = '95dd0062-3349-4741-9ce0-db883f53f475'::text) OR ((subscriber_ueid)::text = ANY ('{0f827f84-0a5f-473d-8321-bee4d15ce092,cc811db3-e041-488d-80e8-7a3b8801fff6,035cd5cd-9c54-439c-8d5c-1d03876d1a25,e58866ad-7a37-4529-b010-18cf2111cc25,6badfea4-cff8-4b75-b2ca-54b20f834e41,60c39c13-c561-508c-954d-e21ce69d317c,60d7c937-dee6-4f7e-a24f-eedd473d6734,abbebec6-6772-415a-b7c5-83b9af824d31,3148c14f-3c52-483b-8ffb-fe112a5bdccf,c26bdf09-8435-4959-ae55-14dbec568694,b38f3c60-fab9-4276-94eb-be048adac51f,61f4b254-3f4e-4a3b-b5e5-44214c6de3ad,a9aef2b6-4f09-5105-957f-071bf70c5915,1b503ea5-53ad-59ad-a06a-9365e3137632,eb9b5de8-232e-7ef9-b97c-478e7ef19525,4f180fee-2613-464d-9526-5872a17189bd,6b42abe9-2805-4fa3-95f9-ce7e25b19fd1,d6fae5d0-2313-45fa-8d1a-92c7eb5167c0,76d9a5f0-c7a7-4ddb-b89e-ba42da954c24,95ac8a70-05d5-4248-a79f-ec6c2aeae8bb,a854298b-3f4a-478d-953b-bad12b0e56b0}'::text[])) OR ((user_ueid)::text = ANY ('{c272ee83-2fef-4683-94a8-98c8ae00b0e0,397daf88-fe77-48c6-a27a-b6072fb6bb6e,gabr}'::text[])))
  • Rows Removed by Filter: 1
Planning time : 3.602 ms
Execution time : 2,223.853 ms