0
explain select count(*) from (SELECT tcy.clientid,
               tcy.projectid,
               max(tcy.lastmodifieddate) AS projectupdatedat
           FROM test_cycles tcy
           JOIN projects prj ON prj.id = tcy.projectid AND prj.clientid = tcy.clientid AND prj.projectstatusid = 101
          WHERE (EXISTS ( SELECT 1
                   FROM client_licenses lic
                  WHERE lic.status::text = 'pro'::text AND lic.packagetype = 5 AND lic.clientid = tcy.clientid)) OR (EXISTS ( SELECT 1
                   FROM insights.test_cycle_control tcc
                  WHERE tcc.clientid = tcy.clientid AND tcc.projectid = 0 AND tcc.action::text = 'include'::text)) OR (EXISTS ( SELECT 1
                   FROM insights.test_cycle_control tcc
                  WHERE tcc.clientid = tcy.clientid AND tcc.projectid = tcy.projectid AND tcc.action::text = 'include'::text))
          GROUP BY tcy.clientid, tcy.projectid) a
Aggregate  (cost=6280314.16..6280314.17 rows=1 width=8)
  ->  GroupAggregate  (cost=1777.59..6279718.58 rows=47646 width=24)
        Group Key: tcy.clientid, tcy.projectid
        ->  Incremental Sort  (cost=1777.59..6279003.89 rows=47646 width=16)
              Sort Key: tcy.clientid, tcy.projectid
              Presorted Key: tcy.clientid
              ->  Nested Loop  (cost=132.81..6276761.99 rows=47646 width=16)
                    ->  Index Scan using idx_projects_cli on projects prj  (cost=0.29..7788.94 rows=29550 width=16)
                          Filter: (projectstatusid = 101)
                    ->  Bitmap Heap Scan on test_cycles tcy  (cost=132.52..212.12 rows=3 width=16)
                          Recheck Cond: ((projectid = prj.id) AND (clientid = prj.clientid))
                          Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4) OR (alternatives: SubPlan 5 or hashed SubPlan 6))
                          ->  BitmapAnd  (cost=132.52..132.52 rows=4 width=0)
                                ->  Bitmap Index Scan on idx_test_cycles_pro  (cost=0.00..19.78 rows=1766 width=0)
                                      Index Cond: (projectid = prj.id)
                                ->  Bitmap Index Scan on idx_test_cycles_cli  (cost=0.00..112.49 rows=13750 width=0)
                                      Index Cond: (clientid = prj.clientid)
                          SubPlan 1
                            ->  Index Scan using idx_client_licenses_cli on client_licenses lic  (cost=0.28..8.31 rows=1 width=0)
                                  Index Cond: (clientid = tcy.clientid)
                                  Filter: (((status)::text = 'pro'::text) AND (packagetype = 5))
                          SubPlan 2
                            ->  Seq Scan on client_licenses lic_1  (cost=0.00..345.62 rows=18 width=8)
                                  Filter: (((status)::text = 'pro'::text) AND (packagetype = 5))
                          SubPlan 3
                            ->  Index Only Scan using das_idx1 on test_cycle_control tcc  (cost=0.27..4.29 rows=1 width=0)
                                  Index Cond: (clientid = tcy.clientid)
                                  Filter: ((projectid = 0) AND ((action)::text = 'include'::text))
                          SubPlan 4
                            ->  Seq Scan on test_cycle_control tcc_1  (cost=0.00..8.27 rows=351 width=8)
                                  Filter: ((projectid = 0) AND ((action)::text = 'include'::text))
                          SubPlan 5
                            ->  Index Only Scan using das_idx1 on test_cycle_control tcc_2  (cost=0.27..4.29 rows=1 width=0)
                                  Index Cond: (clientid = tcy.clientid)
                                  Filter: ((projectid = tcy.projectid) AND ((action)::text = 'include'::text))
                          SubPlan 6
                            ->  Seq Scan on test_cycle_control tcc_3  (cost=0.00..7.39 rows=351 width=16)
                                  Filter: ((action)::text = 'include'::text)

I did add a new INDEX:

Create index idx_test_cycles_clientid_and_projectid_and_lastmodifieddate  on test_cycles (clientid, projectid, lastmodifieddate DESC) WHERE(lastmodifieddate IS NOT NULL)

But it's not getting used

2
  • 1
    Where did you expect that index to get used? For performance problems, you should look at the performance. That is, look at EXPLAIN (ANALYZE, BUFFERS), not just EXPLAIN.
    – jjanes
    Nov 29 '21 at 20:30
  • Many possibilities, one of which is to rewrite as a union of two queries each with three tables: add client_licenses_cli for the first and test_cycle_control for the second (you can combine the last two EXIST two clauses, they have two of their three variables in common). The Nested Loop is killing you. Dec 1 '21 at 23:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.