Get rid of SeqScan on projects

From the fresh postgres checkup – this query is the second in the top list by total_time according to pg_stat_statements:

SELECT COUNT(*) FROM "projects" WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 3361694 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (10,20));

Profile:

#	Calls	▼ Total time	Rows	shared_blks_hit	shared_blks_read	shared_blks_dirtied	shared_blks_written	blk_read_time	blk_write_time	kcache_reads	kcache_writes	kcache_user_time_ms	kcache_system_time	Query
2	61 0.08/sec 1.00/call 0.00%	223,900.88 ms 303ms/sec 3.67s/call 7.22%	61 0.08/sec 1.00/call 0.00%	17,587,016 blks 23.88K blks/sec 288.32K blks/call 1.71%	0 blks 0.00 blks/sec 0.00 blks/call 0.00%	506 blks 0.69 blks/sec 8.30 blks/call 0.09%	0 blks 0.00 blks/sec 0.00 blks/call 0.00%	0.00 ms 0s/sec 0s/call 0.00%	0.00 ms 0s/sec 0s/call 0.00%	0.00 bytes 0.00 bytes/sec 0.00 bytes/call 0.00%	0.00 bytes 0.00 bytes/sec 0.00 bytes/call 0.00%	0.00 ms 0s/sec 0s/call 0.00%	0.00 ms 0s/sec 0s/call 0.00%	SELECT COUNT(*) FROM "projects" WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 3361694 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (10,20))

Execution plan:

                                                                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=32165857.48..32165857.49 rows=1 width=8) (actual time=4045.318..4045.318 rows=1 loops=1)
   Buffers: shared hit=288314
   ->  Seq Scan on projects  (cost=0.00..32153593.26 rows=4905688 width=0) (actual time=0.067..3948.052 rows=1178691 loops=1)
         Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{10,20}'::integer[])))
         Rows Removed by Filter: 7444573
         Buffers: shared hit=288314
         SubPlan 1
           ->  Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations  (cost=0.56..3.58 rows=1 width=0) (never executed)
                 Index Cond: ((user_id = 3361694) AND (project_id = projects.id))
                 Heap Fetches: 0
         SubPlan 2
           ->  Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1  (cost=0.56..8.29 rows=62 width=4) (actual time=0.041..0.042 rows=7 loops=1)
                 Index Cond: (user_id = 3361694)
                 Heap Fetches: 0
                 Buffers: shared hit=5
 Planning time: 0.286 ms
 Execution time: 4045.362 ms
(17 rows)