SQL query takes forever to run

user_12693276 0
Viewed 91

I have written some query a few days back when I had a small database, everything was fine. But, the size of database has grown since then and now it takes a large amount of time to run. Is there any way this query can run faster?

explain SELECT  job.id,  job_title, text_job_description, html_job_description, amp_html_job_desc, destination, created_at,  org.id, org.name, org.logo
FROM jobs.job as job  LEFT OUTER JOIN
     jobs.organization as org
     ON job.organization_id = org.id
WHERE job.is_expired = 0 and job.is_hidden = 0 and 
      deleted_at is NULL
order by job.created_at desc
offset 0 limit 20;

                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Limit  (cost=10514.03..10514.08 rows=20 width=1456)
   ->  Sort  (cost=10514.03..10594.67 rows=32258 width=1456)
         Sort Key: job.created_at DESC
         ->  Hash Left Join  (cost=1.20..9655.65 rows=32258 width=1456)
               Hash Cond: (job.organization_id = org.id)
               ->  Seq Scan on job  (cost=0.00..9529.90 rows=32258 width=1384)
                     Filter: ((deleted_at IS NULL) AND (is_expired = 0) AND (is_hidden = 0))
               ->  Hash  (cost=1.09..1.09 rows=9 width=72)
                     ->  Seq Scan on organization org  (cost=0.00..1.09 rows=9 width=72)
query-performance postgresql sql
1 Answers

Presumably, your query is:

SELECT . . .
FROM jobs.job j LEFT OUTER JOIN
     jobs.organization o
     ON j.organization_id = o.id
WHERE j.is_expired = 0 and j.is_hidden = 0 and 
      j.deleted_at is NULL
------^ not identified in your query
ORDER BY j.created_at desc
OFFSET 0 LIMIT 20;

If so, you want an index on (job(is_expired, is_hidden, deled_at, created_at, organization_id) and on organization(id). The latter is probably not needed if id is defined as a primary key.

user_11440350
Related