postgreSQL查询需要很长时间才能运行

Question

几天前，当我有一个小数据库时，我写了一些查询，一切都很好。但是，从那时起，数据库的大小一直在增长，现在运行起来需要大量的时间。有什么方法可以让这个查询运行得更快吗？

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)

Answer 1

假设您的查询是：

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;

如果是，则需要对(job(is_expired, is_hidden, deled_at, created_at, organization_id)和organization(id)进行索引。如果id被定义为主键，则可能不需要后者。