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应用程序中，这对于单个查询来说是不可接受的。

我按照PostgreSQL wiki上的建议运行了EXPLAIN (ANALYZE, BUFFERS)命令，并将结果放在这里：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。

- nzifnab

Answer 1

< p >使用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）的必要性。

- Gordon Linoff

---

在最后一个create index上出现了语法错误，你漏掉了索引的名称和on。应该像这样：create index jobs_account_id_sop_id on jobs(account_id, service_of_process_id); - nzifnab

Answer 2

我会尝试将查询修改为以下内容：

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 and jobs.account_id = 250093
WHERE
  (court_cases.account_id = 250093)
ORDER BY
  court_cases.court_date DESC NULLS LAST,
  court_cases.id DESC
LIMIT 30
OFFSET 0;

我认为问题在于where过滤器没有被查询计划优化器正确分解，这是一个非常奇怪的性能bug。

- morepaolo

---

这不起作用 :\ 它没有包括那些 jobs.account_id = 250093 但 court_cases.account_id != 250093 的法庭案件，这就是为什么原始查询中有一个 OR :( - nzifnab

你是正确的... 事实上，问题在于where条件只能在完整的连接集计算完成后进行评估，这可能需要很长时间，因为表很大。这不是索引问题...我会尝试将查询分解为两个子查询，拆分where过滤器，然后应用来自两个查询联合的select distinct。 - morepaolo