sql - Postgres 左外连接似乎没有使用表索引

Question

让我知道是否应该将其发布在 DBA.stackexchange.com 上...

我有以下查询:

SELECT DISTINCT "court_cases".*
FROM "court_cases"
LEFT OUTER JOIN service_of_processes
  ON service_of_processes.court_case_id = court_cases.id
LEFT OUTER JOIN jobs
  ON jobs.service_of_process_id = service_of_processes.id
WHERE
  (jobs.account_id = 250093
  OR court_cases.account_id = 250093)
ORDER BY
  court_cases.court_date DESC NULLS LAST,
  court_cases.id DESC
LIMIT 30
OFFSET 0;

但它需要 2-4 秒才能运行，在 Web 应用程序中，这对于单个查询来说是 Not Acceptable 。

我按照建议对查询运行了 EXPLAIN (ANALYZE, BUFFERS) on the PostgreSQL wiki ，并将结果放在这里:http://explain.depesz.com/s/Yn6

查询涉及到的表的表定义在这里(包括外键关系的索引):

http://sqlfiddle.com/#!15/114c6

是否因为 WHERE 子句从两个不同的表进行查询而导致使用索引出现问题？我可以对查询进行何种索引或更改以加快此运行速度？

这些是相关表格的当前大小:

PSQL=# select count(*) from service_of_processes;
 count  
--------
 103787
(1 row)

PSQL=# select count(*) from jobs;
 count  
--------
 108995
(1 row)

PSQL=# select count(*) from court_cases;
 count 
-------
 84410
(1 row)

编辑:如果重要的话，我正在使用 Postgresql 9.3.1。

Answer 1

or子句会使优化查询变得困难。一种想法是将查询的两个部分拆分为两个单独的子查询。这实际上大大简化了其中一个(court_cases.account_id 上的那个)。

试试这个版本:

(SELECT cc.*
 FROM "court_cases" cc
 WHERE cc.account_id = 250093
 ORDER BY cc.court_date DESC NULLS LAST,
          cc.id DESC
 LIMIT 30
) UNION ALL
(SELECT cc.*
 FROM "court_cases" cc LEFT OUTER JOIN
      service_of_processes sop
      ON sop.court_case_id = cc.id LEFT OUTER JOIN
      jobs j
      ON j.service_of_process_id = sop.id
 WHERE (j.account_id = 250093 AND cc.account_id <> 250093)
 ORDER BY cc.court_date DESC NULLS LAST, id DESC
 LIMIT 30
)
ORDER BY court_date DESC NULLS LAST,
         id DESC
LIMIT 30 OFFSET 0;

并添加以下索引:

create index court_cases_accountid_courtdate_id on court_cases(account_id, court_date, id);
create index jobs_accountid_sop on jobs(account_id, service_of_process_id);

请注意，第二个查询使用 and cc.count_id <> 250093 ，这样可以防止重复记录。这消除了对 distinct 的需要或 union (没有 union all )。