explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EaJv4

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 1,969.526 ↑ 1.0 1 1

Limit (cost=481,528.99..481,529.00 rows=1 width=213) (actual time=1,969.524..1,969.526 rows=1 loops=1)

2.          

CTE user_org_ueids

3. 0.109 0.109 ↑ 1.0 1 1

Index Scan using idx_om_user_organization_external_id on om_user_organization ouo_2 (cost=0.43..11.31 rows=1 width=37) (actual time=0.027..0.109 rows=1 loops=1)

  • Index Cond: (external_id = ANY ('{50000048,460044,50000028,50000021}'::integer[]))
  • Filter: (fulltext_string @@ plainto_tsquery('БЕЛЬБЕК'::text))
  • Rows Removed by Filter: 3
4. 0.016 1,969.522 ↑ 1.0 1 1

Sort (cost=481,517.68..481,517.69 rows=1 width=213) (actual time=1,969.521..1,969.522 rows=1 loops=1)

  • Sort Key: ouo.name
  • Sort Method: quicksort Memory: 26kB
5. 4.869 1,969.506 ↑ 1.0 1 1

Nested Loop Left Join (cost=480,065.07..481,517.67 rows=1 width=213) (actual time=1,965.483..1,969.506 rows=1 loops=1)

  • Join Filter: ((ouo.unique_exchange_id)::text = (COALESCE(ouo_1.unique_exchange_id, t2.ououeid))::text)
  • Rows Removed by Join Filter: 5,786
  • Filter: ((t2.subscriber_packages_infos 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))
6. 0.130 0.130 ↑ 1.0 1 1

Index Scan using idx_om_user_organization_external_id on om_user_organization ouo (cost=0.43..11.31 rows=1 width=85) (actual time=0.061..0.130 rows=1 loops=1)

  • Index Cond: (external_id = ANY ('{50000048,460044,50000028,50000021}'::integer[]))
  • Filter: (fulltext_string @@ plainto_tsquery('БЕЛЬБЕК'::text))
  • Rows Removed by Filter: 3
7. 10.621 1,964.507 ↑ 3.7 5,787 1

Merge Full Join (cost=480,064.64..481,239.56 rows=21,344 width=197) (actual time=1,912.158..1,964.507 rows=5,787 loops=1)

  • Merge Cond: ((t2.ououeid)::text = (ouo_1.unique_exchange_id)::text)
8. 0.042 1,245.743 ↑ 100.0 2 1

Sort (cost=110,725.67..110,726.17 rows=200 width=64) (actual time=1,245.740..1,245.743 rows=2 loops=1)

  • Sort Key: t2.ououeid
  • Sort Method: quicksort Memory: 25kB
9. 0.010 1,245.701 ↑ 100.0 2 1

Subquery Scan on t2 (cost=110,713.53..110,718.03 rows=200 width=64) (actual time=1,245.693..1,245.701 rows=2 loops=1)

10. 0.114 1,245.691 ↑ 100.0 2 1

HashAggregate (cost=110,713.53..110,716.03 rows=200 width=64) (actual time=1,245.687..1,245.691 rows=2 loops=1)

  • Group Key: (unnest(u.user_organization_ueids))
11. 0.037 1,245.577 ↑ 18,366.7 3 1

HashAggregate (cost=109,469.65..109,749.28 rows=55,100 width=96) (actual time=1,245.569..1,245.577 rows=3 loops=1)

  • Group Key: unnest(u.user_organization_ueids), u.subscriber_ueid, op2.start_date, op2.end_date
12. 0.083 1,245.540 ↑ 18,366.7 3 1

Nested Loop (cost=1.31..108,918.65 rows=55,100 width=96) (actual time=525.344..1,245.540 rows=3 loops=1)

13. 0.098 1,225.359 ↑ 2,045.0 2 1

Nested Loop (cost=0.88..104,493.55 rows=4,090 width=84) (actual time=525.281..1,225.359 rows=2 loops=1)

14. 522.758 1,224.973 ↑ 7,003.8 4 1

Nested Loop (cost=0.00..48,453.43 rows=28,015 width=115) (actual time=417.207..1,224.973 rows=4 loops=1)

  • Join Filter: ((uou.unique_exchange_id)::text = ANY ((u.user_organization_ueids)::text[]))
  • Rows Removed by Join Filter: 573,029
15. 0.117 0.117 ↑ 1.0 1 1

CTE Scan on user_org_ueids uou (cost=0.00..0.02 rows=1 width=90) (actual time=0.032..0.117 rows=1 loops=1)

16. 702.098 702.098 ↓ 1.0 573,033 1

Seq Scan on om_user u (cost=0.00..35,560.28 rows=573,028 width=115) (actual time=0.007..702.098 rows=573,033 loops=1)

17. 0.048 0.288 ↓ 0.0 0 4

Bitmap Heap Scan on om_options_package_owner opo2 (cost=0.88..1.99 rows=1 width=56) (actual time=0.071..0.072 rows=0 loops=4)

  • Recheck Cond: ((subscriber_ueid = u.subscriber_ueid) OR ((user_ueid)::text = (u.ueid)::text))
  • Heap Blocks: exact=2
18. 0.044 0.240 ↓ 0.0 0 4

BitmapOr (cost=0.88..0.88 rows=1 width=0) (actual time=0.060..0.060 rows=0 loops=4)

19. 0.076 0.076 ↓ 0.0 0 4

Bitmap Index Scan on ix_om_options_package_owner_subscriber_ueid (cost=0.00..0.44 rows=1 width=0) (actual time=0.019..0.019 rows=0 loops=4)

  • Index Cond: (subscriber_ueid = u.subscriber_ueid)
20. 0.120 0.120 ↓ 0.0 0 4

Bitmap Index Scan on om_options_package_owner_user_ueid_key (cost=0.00..0.44 rows=1 width=0) (actual time=0.030..0.030 rows=0 loops=4)

  • Index Cond: ((user_ueid)::text = (u.ueid)::text)
21. 20.098 20.098 ↑ 1.0 1 2

Index Scan using ix_om_options_package_fk_om_options_package_owner on om_options_package op2 (cost=0.43..1.00 rows=1 width=20) (actual time=10.042..10.049 rows=1 loops=2)

  • Index Cond: (options_package_owner_id = opo2.id)
  • Filter: ((tariff_ueid)::text = '5734cd43-de6e-4e23-9fe7-bb86398c27a3'::text)
  • Rows Removed by Filter: 3,562
22. 11.900 708.143 ↑ 3.7 5,787 1

Materialize (cost=369,338.96..370,246.08 rows=21,344 width=133) (actual time=666.408..708.143 rows=5,787 loops=1)

23. 23.386 696.243 ↑ 3.7 5,787 1

GroupAggregate (cost=369,338.96..369,979.28 rows=21,344 width=129) (actual time=666.398..696.243 rows=5,787 loops=1)

  • Group Key: ouo_1.unique_exchange_id
24. 41.471 672.857 ↑ 2.8 7,570 1

Sort (cost=369,338.96..369,392.32 rows=21,344 width=129) (actual time=666.349..672.857 rows=7,570 loops=1)

  • Sort Key: ouo_1.unique_exchange_id
  • Sort Method: quicksort Memory: 2,706kB
25. 28.525 631.386 ↑ 2.8 7,570 1

Nested Loop (cost=332,316.86..367,804.17 rows=21,344 width=129) (actual time=246.110..631.386 rows=7,570 loops=1)

26. 32.877 480.269 ↑ 3.3 7,662 1

Nested Loop (cost=332,316.43..354,633.87 rows=25,297 width=129) (actual time=246.081..480.269 rows=7,662 loops=1)

27. 121.139 381.832 ↑ 3.1 8,195 1

GroupAggregate (cost=332,316.01..337,152.41 rows=25,297 width=130) (actual time=245.849..381.832 rows=8,195 loops=1)

  • Group Key: op_in.id
28. 45.720 260.693 ↑ 13.0 18,700 1

Sort (cost=332,316.01..332,925.61 rows=243,841 width=130) (actual time=245.743..260.693 rows=18,700 loops=1)

  • Sort Key: op_in.id
  • Sort Method: quicksort Memory: 5,641kB
29. 35.267 214.973 ↑ 13.0 18,700 1

Hash Join (cost=450.36..300,733.48 rows=243,841 width=130) (actual time=4.106..214.973 rows=18,700 loops=1)

  • Hash Cond: (o.option_type_id = ot.id)
30. 43.843 179.079 ↑ 13.0 18,700 1

Nested Loop (cost=430.94..297,361.24 rows=243,841 width=107) (actual time=3.444..179.079 rows=18,700 loops=1)

31. 34.778 36.896 ↑ 3.1 8,195 1

Bitmap Heap Scan on om_options_package op_in (cost=430.37..26,135.84 rows=25,297 width=98) (actual time=3.401..36.896 rows=8,195 loops=1)

  • Recheck Cond: ((tariff_ueid)::text = ANY ('{b55d396d-e963-4ea9-adb2-04a89de46acf,1d3eb6e0-0b35-40fc-a6d2-b2a2af2dffd8}'::text[]))
  • Heap Blocks: exact=6,776
32. 2.118 2.118 ↑ 3.1 8,196 1

Bitmap Index Scan on ix_om_options_package_tariff_end_date_index (cost=0.00..424.05 rows=25,297 width=0) (actual time=2.118..2.118 rows=8,196 loops=1)

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

Index Scan using ix_option_fk_option_options_package_id on om_option o (cost=0.56..10.47 rows=25 width=13) (actual time=0.009..0.012 rows=2 loops=8,195)

  • Index Cond: (options_package_id = op_in.id)
34. 0.108 0.627 ↓ 1.0 126 1

Hash (cost=17.86..17.86 rows=125 width=31) (actual time=0.627..0.627 rows=126 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
35. 0.225 0.519 ↓ 1.0 126 1

Hash Join (cost=7.89..17.86 rows=125 width=31) (actual time=0.191..0.519 rows=126 loops=1)

  • Hash Cond: (ot.option_type_nick_id = otn.id)
36. 0.132 0.132 ↓ 1.0 126 1

Seq Scan on om_option_type ot (cost=0.00..8.25 rows=125 width=14) (actual time=0.011..0.132 rows=126 loops=1)

37. 0.076 0.162 ↑ 1.0 84 1

Hash (cost=6.84..6.84 rows=84 width=25) (actual time=0.162..0.162 rows=84 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
38. 0.086 0.086 ↑ 1.0 84 1

Seq Scan on om_option_type_nick otn (cost=0.00..6.84 rows=84 width=25) (actual time=0.006..0.086 rows=84 loops=1)

39. 65.560 65.560 ↑ 1.0 1 8,195

Index Only Scan using _ix_spark_report_speed_up on om_options_package_owner opo (cost=0.42..0.67 rows=1 width=41) (actual time=0.008..0.008 rows=1 loops=8,195)

  • Index Cond: (id = op_in.options_package_owner_id)
  • Heap Fetches: 26
40. 122.592 122.592 ↑ 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.015..0.016 rows=1 loops=7,662)

  • Index Cond: (unique_exchange_id = (opo.user_organization_ueid)::text)
  • Heap Fetches: 7,570
Planning time : 4.240 ms
Execution time : 1,970.285 ms