0 
WITH queued_logins AS
(
  SELECT a.id AS account_id,
         apps.id AS app_id,
         apps.name AS app_name,
         COUNT(l.id) AS queued_count
  FROM logins l
    LEFT JOIN throttled_requests tr ON tr.login_id = l.id
    LEFT JOIN queued_messages qm
           ON qm.message ilike E'%login_id%22%0C' || l.id || '%'
          AND qm.message ilike E'%command%22%0Eprovision%'
    INNER JOIN users u ON u.id = l.user_id
    INNER JOIN accounts a ON a.id = l.account_id
    INNER JOIN apps ON apps.id = l.app_id
  WHERE l.provisioning_state IN ('provisioning', 'modifying', 'deleting')
    AND (tr.id IS NOT NULL OR qm.id IS NOT NULL)
  GROUP BY 1,
           2
),
stuck_logins AS
(
  SELECT a.id AS account_id,
         apps.id AS app_id,
         apps.name AS app_name,
         COUNT(l.id) AS stuck_count
  FROM logins l
    LEFT JOIN throttled_requests tr ON tr.login_id = l.id
    LEFT JOIN queued_messages qm
           ON qm.message ilike E'%login_id%22%0C' || l.id || '%'
          AND qm.message ilike E'%command%22%0Eprovision%'
    INNER JOIN users u ON u.id = l.user_id
    INNER JOIN accounts a ON a.id = l.account_id
    INNER JOIN apps ON apps.id = l.app_id
  WHERE l.provisioning_state IN ('provisioning', 'modifying', 'deleting')
    AND tr.id IS NULL
    AND qm.id IS NULL
  GROUP BY 1,
           2
)
SELECT a.name AS account_name,
       COALESCE(q.app_id,s.app_id) AS app_id,
       COALESCE(q.app_name,s.app_name) AS app_name,
       COALESCE(q.queued_count,0) AS queued_count,
       COALESCE(s.stuck_count,0) AS stuck_count
FROM accounts a
  LEFT JOIN queued_logins q ON q.account_id = a.id
  LEFT JOIN stuck_logins s ON s.account_id = a.id
WHERE q.queued_count IS NOT NULL
   OR s.stuck_count IS NOT NULL
ORDER BY 1, 3

Explain PLAN

Sort  (cost=1416168.51..1416444.41 rows=110360 width=549) (actual time=53552.665..53552.722 rows=426 loops=1)
   Sort Key: a.name, (COALESCE(q.app_name, s.app_name))
   Sort Method: quicksort  Memory: 69kB
   CTE queued_logins
     ->  GroupAggregate  (cost=852887.19..854681.85 rows=89733 width=29) (actual time=51290.425..51290.425 rows=0 loops=1)
           Group Key: a_1.id, apps.id
           ->  Sort  (cost=852887.19..853111.53 rows=89733 width=25) (actual time=51290.423..51290.423 rows=0 loops=1)
                 Sort Key: a_1.id, apps.id
                 Sort Method: quicksort  Memory: 25kB
                 ->  Nested Loop Left Join  (cost=312661.81..845505.15 rows=89733 width=25) (actual time=51290.407..51290.407 rows=0 loops=1)
                       Join Filter: (qm.message ~~* (('%login_id%22%0C'::text || (l.id)::text) || '%'::text))
                       Filter: ((tr.id IS NOT NULL) OR (qm.id IS NOT NULL))
                       Rows Removed by Filter: 101466
                       ->  Gather  (cost=312661.81..843260.82 rows=89733 width=29) (actual time=4061.263..51222.045 rows=101466 loops=1)
                             Workers Planned: 2
                             Workers Launched: 2
                             ->  Nested Loop  (cost=311661.81..833287.52 rows=37389 width=29) (actual time=4049.833..51178.207 rows=33822 loops=3)
                                   ->  Hash Left Join  (cost=311661.37..570662.36 rows=37389 width=33) (actual time=4046.690..12939.959 rows=33823 loops=3)
                                         Hash Cond: (l.id = tr.login_id)
                                         ->  Hash Join  (cost=311632.02..570492.79 rows=37389 width=29) (actual time=4046.648..12915.500 rows=33823 loops=3)
                                               Hash Cond: (l.app_id = apps.id)
                                               ->  Hash Join  (cost=289878.94..548266.43 rows=37488 width=16) (actual time=3075.951..11916.082 rows=33823 loops=3)
                                                     Hash Cond: (l.account_id = a_1.id)
                                                     ->  Parallel Bitmap Heap Scan on logins l  (cost=267457.77..525370.72 rows=37587 width=16) (actual time=2886.931..11681.832 rows=33823 loops=3)
                                                           Recheck Cond: (provisioning_state = ANY ('{provisioning,modifying,deleting}'::text[]))
                                                           Heap Blocks: exact=17337
                                                           ->  Bitmap Index Scan on index_logins_on_account_id_and_provisioning_state  (cost=0.00..267435.22 rows=90208 width=0) (actual time=2882.926..2882.926 rows=101468 loops=1)
                                                                 Index Cond: (provisioning_state = ANY ('{provisioning,modifying,deleting}'::text[]))
                                                     ->  Hash  (cost=21041.63..21041.63 rows=110363 width=4) (actual time=188.378..188.378 rows=110363 loops=3)
                                                           Buckets: 131072  Batches: 1  Memory Usage: 4904kB
                                                           ->  Seq Scan on accounts a_1  (cost=0.00..21041.63 rows=110363 width=4) (actual time=0.023..123.462 rows=110363 loops=3)
                                               ->  Hash  (cost=15783.59..15783.59 rows=477559 width=17) (actual time=967.180..967.180 rows=477559 loops=3)
                                                     Buckets: 524288  Batches: 1  Memory Usage: 27141kB
                                                     ->  Seq Scan on apps  (cost=0.00..15783.59 rows=477559 width=17) (actual time=0.319..710.916 rows=477559 loops=3)
                                         ->  Hash  (cost=18.60..18.60 rows=860 width=8) (actual time=0.013..0.013 rows=0 loops=3)
                                               Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                               ->  Seq Scan on throttled_requests tr  (cost=0.00..18.60 rows=860 width=8) (actual time=0.013..0.013 rows=0 loops=3)
                                   ->  Index Only Scan using users_pkey on users u  (cost=0.44..7.02 rows=1 width=4) (actual time=1.129..1.129 rows=1 loops=101468)
                                         Index Cond: (id = l.user_id)
                                         Heap Fetches: 34022
                       ->  Materialize  (cost=0.00..1.00 rows=1 width=36) (actual time=0.000..0.000 rows=0 loops=101466)
                             ->  Seq Scan on queued_messages qm  (cost=0.00..1.00 rows=1 width=36) (actual time=0.011..0.011 rows=0 loops=1)
                                   Filter: (message ~~* '%command%22%0Eprovision%'::text)
   CTE stuck_logins
     ->  GroupAggregate  (cost=526480.36..526480.38 rows=1 width=29) (actual time=1943.682..1983.118 rows=426 loops=1)
           Group Key: a_2.id, apps_1.id
           ->  Sort  (cost=526480.36..526480.36 rows=1 width=25) (actual time=1943.619..1959.165 rows=101466 loops=1)
                 Sort Key: a_2.id, apps_1.id
                 Sort Method: quicksort  Memory: 10415kB
                 ->  Nested Loop  (cost=267488.40..526480.35 rows=1 width=25) (actual time=372.826..1901.909 rows=101466 loops=1)
                       ->  Nested Loop  (cost=267487.98..526475.17 rows=1 width=12) (actual time=371.479..1444.521 rows=101466 loops=1)
                             ->  Nested Loop  (cost=267487.56..526469.95 rows=1 width=12) (actual time=370.870..1205.355 rows=101466 loops=1)
                                   ->  Nested Loop Left Join  (cost=267487.12..526462.93 rows=1 width=16) (actual time=370.840..720.037 rows=101468 loops=1)
                                         Join Filter: (qm_1.message ~~* (('%login_id%22%0C'::text || (l_1.id)::text) || '%'::text))
                                         Filter: (qm_1.id IS NULL)
                                         ->  Hash Left Join  (cost=267487.12..526461.91 rows=1 width=16) (actual time=370.832..552.520 rows=101468 loops=1)
                                               Hash Cond: (l_1.id = tr_1.login_id)
                                               Filter: (tr_1.id IS NULL)
                                               ->  Bitmap Heap Scan on logins l_1  (cost=267457.77..526094.27 rows=90208 width=16) (actual time=370.798..506.565 rows=101468 loops=1)
                                                     Recheck Cond: (provisioning_state = ANY ('{provisioning,modifying,deleting}'::text[]))
                                                     Heap Blocks: exact=51506
                                                     ->  Bitmap Index Scan on index_logins_on_account_id_and_provisioning_state  (cost=0.00..267435.22 rows=90208 width=0) (actual time=361.154..361.154 rows=101468 loops=1)
                                                           Index Cond: (provisioning_state = ANY ('{provisioning,modifying,deleting}'::text[]))
                                               ->  Hash  (cost=18.60..18.60 rows=860 width=8) (actual time=0.007..0.007 rows=0 loops=1)
                                                     Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                                     ->  Seq Scan on throttled_requests tr_1  (cost=0.00..18.60 rows=860 width=8) (actual time=0.007..0.007 rows=0 loops=1)
                                         ->  Seq Scan on queued_messages qm_1  (cost=0.00..1.00 rows=1 width=36) (actual time=0.001..0.001 rows=0 loops=101468)
                                               Filter: (message ~~* '%command%22%0Eprovision%'::text)
                                   ->  Index Only Scan using users_pkey on users u_1  (cost=0.44..7.02 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=101468)
                                         Index Cond: (id = l_1.user_id)
                                         Heap Fetches: 101466
                             ->  Index Only Scan using accounts_pkey on accounts a_2  (cost=0.42..5.21 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=101466)
                                   Index Cond: (id = l_1.account_id)
                                   Heap Fetches: 101466
                       ->  Index Scan using services_pkey on apps apps_1  (cost=0.42..5.18 rows=1 width=17) (actual time=0.004..0.004 rows=1 loops=101466)
                             Index Cond: (id = l_1.app_id)
   ->  Hash Left Join  (cost=22421.20..25762.60 rows=110360 width=549) (actual time=53494.239..53548.903 rows=426 loops=1)
         Hash Cond: (a.id = s.account_id)
         Filter: ((q.queued_count IS NOT NULL) OR (s.stuck_count IS NOT NULL))
         Rows Removed by Filter: 110108
         ->  Hash Right Join  (cost=22421.17..25348.70 rows=110363 width=545) (actual time=51510.610..51543.604 rows=110363 loops=1)
               Hash Cond: (q.account_id = a.id)
               ->  CTE Scan on queued_logins q  (cost=0.00..1794.66 rows=89733 width=532) (actual time=51290.428..51290.428 rows=0 loops=1)
               ->  Hash  (cost=21041.63..21041.63 rows=110363 width=17) (actual time=219.703..219.703 rows=110363 loops=1)
                     Buckets: 131072  Batches: 1  Memory Usage: 6520kB
                     ->  Seq Scan on accounts a  (cost=0.00..21041.63 rows=110363 width=17) (actual time=0.011..184.185 rows=110363 loops=1)
         ->  Hash  (cost=0.02..0.02 rows=1 width=532) (actual time=1983.547..1983.547 rows=426 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 35kB
               ->  CTE Scan on stuck_logins s  (cost=0.00..0.02 rows=1 width=532) (actual time=1943.686..1983.377 rows=426 loops=1)
 Planning time: 71.125 ms
 Execution time: 53557.095 ms
Improve this question
0

1 Answer 1

Reset to default
4

Replace queued_logins and stuck_logins by one query, since they are almost equal. Replace the count by two counts:

sum(case when tr.id is null and qm.id is null then 1 else 0 end)
    stuck_count,
sum(case when tr.id is not null or qm.id is not null then 1 else 0 end)
    queued_count

This should make your query at least twice as fast, if not much more.

The result should be somewhat like

WITH
  queued_logins AS (
      SELECT a.id AS account_id, apps.id AS app_id, apps.name AS app_name,
        sum(case when tr.id is null and qm.id is null then 1 else 0 end)
               stuck_count,
        sum(case when tr.id is not null or qm.id is not null then 1 else 0 end)
               queued_count
        FROM logins l
        LEFT JOIN throttled_requests tr ON tr.login_id = l.id
        LEFT JOIN queued_messages qm
          ON qm.message ILIKE E'%login_id%22%0C' || l.id || '%'
          AND qm.message ILIKE E'%command%22%0Eprovision%'
        INNER JOIN users u ON u.id = l.user_id
        INNER JOIN accounts a ON a.id = l.account_id
        INNER JOIN apps ON apps.id = l.app_id
        WHERE l.provisioning_state IN ('provisioning', 'modifying', 'deleting')
        GROUP BY 1, 2)
 SELECT a.name AS account_name,
        q.app_id AS app_id,
        q.app_name AS app_name,
        q.queued_count AS queued_count,
        q.stuck_count AS stuck_count
   FROM accounts a
   LEFT JOIN queued_logins q ON q.account_id = a.id
   WHERE q.queued_count IS NOT NULL
      OR q.stuck_count IS NOT NULL
   ORDER BY 1, 3;
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.