explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CORh

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 7,220.998 ↑ 1.0 10 1

Limit (cost=30,000,075,928.77..229,998,562,697.18 rows=10 width=228) (actual time=806.388..7,220.998 rows=10 loops=1)

2. 25.365 7,220.982 ↑ 8,800.7 10 1

Nested Loop Left Join (cost=30,000,075,928.77..1,760,156,682,578,689.50 rows=88,007 width=228) (actual time=806.387..7,220.982 rows=10 loops=1)

3. 0.039 7,106.577 ↑ 8,800.7 10 1

Nested Loop Left Join (cost=30,000,072,475.37..1,760,156,378,348,584.00 rows=88,007 width=159) (actual time=741.113..7,106.577 rows=10 loops=1)

4. 0.037 4,704.328 ↑ 8,800.7 10 1

Nested Loop Left Join (cost=20,000,018,872.18..880,081,660,889,660.12 rows=88,007 width=151) (actual time=526.157..4,704.328 rows=10 loops=1)

5. 0.011 0.011 ↑ 8,800.7 10 1

Seq Scan on crmuser cu (cost=10,000,000,000.00..10,000,003,444.07 rows=88,007 width=143) (actual time=0.004..0.011 rows=10 loops=1)

6. 0.220 4,704.280 ↑ 1.0 1 10

Aggregate (cost=10,000,018,872.18..10,000,018,872.19 rows=1 width=8) (actual time=470.427..470.428 rows=1 loops=10)

7. 3,720.470 4,704.060 ↑ 5,535.8 14 10

Merge Left Join (cost=10,000,005,967.91..10,000,018,678.42 rows=77,501 width=8) (actual time=321.653..470.406 rows=14 loops=10)

  • Merge Cond: (u.id = ct.userid)
  • Filter: ((ct.id IS NOT NULL) OR ((lower("substring"(cu.email, '@(.*)'::text)) = lower("substring"(u.username, '@(.*)'::text))) AND (lower("substring"(cu.email, '@(.*)'::text)) <> ALL ('{salesforce.com,gmail.com,yahoo.com,hotmail.com}'::text[]))))
  • Rows Removed by Filter: 77,596
8. 824.990 824.990 ↓ 1.0 77,600 10

Index Scan using user_pkey on "user" u (cost=0.42..12,511.63 rows=77,501 width=33) (actual time=0.030..82.499 rows=77,600 loops=10)

9. 0.160 158.600 ↑ 11.4 13 10

Sort (cost=10,000,005,967.49..10,000,005,967.86 rows=148 width=16) (actual time=15.857..15.860 rows=13 loops=10)

  • Sort Key: ct.userid
  • Sort Method: quicksort Memory: 26kB
10. 158.440 158.440 ↑ 11.4 13 10

Seq Scan on crmtoken ct (cost=10,000,000,000.00..10,000,005,962.16 rows=148 width=16) (actual time=5.755..15.844 rows=13 loops=10)

  • Filter: (COALESCE(sforgid, (hubid)::text) = cu.crmid)
  • Rows Removed by Filter: 29,596
11. 0.030 2,402.210 ↑ 1.0 1 10

Aggregate (cost=10,000,053,603.19..10,000,053,603.20 rows=1 width=8) (actual time=240.221..240.221 rows=1 loops=10)

12. 0.080 2,402.180 ↑ 5,852.0 1 10

Unique (cost=10,000,000,001.98..10,000,053,530.04 rows=5,852 width=12) (actual time=184.786..240.218 rows=1 loops=10)

13. 812.950 2,402.100 ↑ 328.1 19 10

Nested Loop Left Join (cost=10,000,000,001.98..10,000,053,514.46 rows=6,234 width=12) (actual time=184.780..240.210 rows=19 loops=10)

  • Join Filter: (ct_1.userid = u_1.id)
  • Rows Removed by Join Filter: 197,651
  • Filter: ((ct_1.id IS NOT NULL) OR ((lower("substring"(cu.email, '@(.*)'::text)) = lower("substring"(u_1.username, '@(.*)'::text))) AND (lower("substring"(cu.email, '@(.*)'::text)) <> ALL ('{salesforce.com,gmail.com,yahoo.com,hotmail.com}'::text[]))))
  • Rows Removed by Filter: 14,969
14. 97.010 1,289.650 ↓ 2.4 14,975 10

Nested Loop (cost=1.97..12,953.23 rows=6,234 width=45) (actual time=0.079..128.965 rows=14,975 loops=10)

15. 61.860 743.390 ↓ 2.4 14,975 10

Nested Loop (cost=1.56..9,384.20 rows=6,234 width=20) (actual time=0.067..74.339 rows=14,975 loops=10)

16. 36.320 507.260 ↑ 1.0 5,809 10

Nested Loop (cost=1.14..6,691.14 rows=5,852 width=28) (actual time=0.056..50.726 rows=5,809 loops=10)

17. 31.380 354.760 ↑ 1.0 5,809 10

Nested Loop (cost=0.85..4,231.48 rows=5,852 width=20) (actual time=0.046..35.476 rows=5,809 loops=10)

18. 44.300 207.200 ↑ 1.0 5,809 10

Nested Loop (cost=0.56..2,386.71 rows=5,852 width=20) (actual time=0.036..20.720 rows=5,809 loops=10)

19. 46.720 46.720 ↑ 1.0 5,809 10

Index Scan using orgstripeplan_pkey on orgstripeplan osp (cost=0.28..259.20 rows=5,852 width=20) (actual time=0.025..4.672 rows=5,809 loops=10)

20. 116.180 116.180 ↑ 1.0 1 58,090

Index Scan using orgstripesubscription_pkey on orgstripesubscription oss (cost=0.28..0.36 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=58,090)

  • Index Cond: (id = osp.orgstripesubscriptionid)
21. 116.180 116.180 ↑ 1.0 1 58,090

Index Scan using orgstripecustomer_pkey on orgstripecustomer osc (cost=0.28..0.32 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=58,090)

  • Index Cond: (id = oss.orgstripecustomerid)
22. 116.180 116.180 ↑ 1.0 1 58,090

Index Only Scan using org_pkey on org o (cost=0.29..0.42 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=58,090)

  • Index Cond: (id = osc.orgid)
  • Heap Fetches: 15,500
23. 174.270 174.270 ↓ 3.0 3 58,090

Index Only Scan using orguserrole_orgid_userid_roleid_key on orguserrole our (cost=0.42..0.45 rows=1 width=16) (actual time=0.003..0.003 rows=3 loops=58,090)

  • Index Cond: (orgid = o.id)
  • Heap Fetches: 9,120
24. 449.250 449.250 ↑ 1.0 1 149,750

Index Scan using user_pkey on "user" u_1 (cost=0.42..0.57 rows=1 width=33) (actual time=0.003..0.003 rows=1 loops=149,750)

  • Index Cond: (id = our.userid)
25. 147.150 299.500 ↑ 11.4 13 149,750

Materialize (cost=10,000,000,000.00..10,000,005,962.90 rows=148 width=16) (actual time=0.000..0.002 rows=13 loops=149,750)

26. 152.350 152.350 ↑ 11.4 13 10

Seq Scan on crmtoken ct_1 (cost=10,000,000,000.00..10,000,005,962.16 rows=148 width=16) (actual time=5.512..15.235 rows=13 loops=10)

  • Filter: (COALESCE(sforgid, (hubid)::text) = cu.crmid)
  • Rows Removed by Filter: 29,596
27. 0.040 53.830 ↑ 1.0 1 10

Aggregate (cost=3,453.40..3,453.41 rows=1 width=8) (actual time=5.383..5.383 rows=1 loops=10)

28. 0.224 53.790 ↑ 25.8 4 10

Nested Loop (cost=0.84..3,453.15 rows=103 width=0) (actual time=1.355..5.379 rows=4 loops=10)

29. 52.390 52.390 ↑ 6.9 15 10

Index Scan using crmuser_crmtype_crmid_crmuserid_key on crmuser cu2 (cost=0.42..3,106.58 rows=103 width=25) (actual time=0.814..5.239 rows=15 loops=10)

  • Index Cond: (crmid = cu.crmid)
  • Filter: (email <> cu.email)
  • Rows Removed by Filter: 1
30. 1.176 1.176 ↓ 0.0 0 147

Index Scan using idx_user_username_lowercase_uniq on "user" u_2 (cost=0.42..3.36 rows=1 width=25) (actual time=0.008..0.008 rows=0 loops=147)

  • Index Cond: (lower(username) = lower(cu2.email))
31.          

SubPlan (for Nested Loop Left Join)

32. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_user_username on "user" (cost=0.42..3.44 rows=1 width=0) (never executed)

  • Index Cond: (username = cu.email)
  • Heap Fetches: 0
33. 28.505 35.210 ↓ 1.0 77,600 1

Bitmap Heap Scan on "user" user_1 (cost=1,718.30..9,623.31 rows=77,501 width=25) (actual time=7.575..35.210 rows=77,600 loops=1)

  • Heap Blocks: exact=7,128
34. 6.705 6.705 ↓ 1.0 78,169 1

Bitmap Index Scan on idx_user_username (cost=0.00..1,698.92 rows=77,501 width=0) (actual time=6.705..6.705 rows=78,169 loops=1)

Planning time : 3.271 ms
Execution time : 7,222.123 ms