指望有条件的大表联接很慢

Question

当表较小时，此查询具有合理的时间。我正在尝试确定什么是瓶颈，但是我不确定如何分析EXPLAIN结果。

SELECT
  COUNT(*)
FROM performance_analyses
INNER JOIN total_sales ON total_sales.id = performance_analyses.total_sales_id
WHERE
  (size > 0) AND
  total_sales.customer_id IN (
    SELECT customers.id FROM customers WHERE customers.active = 't'
    AND customers.visible = 't' AND customers.organization_id = 3
  ) AND
  total_sales.product_category_id IN (
    SELECT product_categories.id FROM product_categories
    WHERE product_categories.organization_id = 3
  ) AND
  total_sales.period_id = 193;

我已经尝试了INNER JOIN’ingcustomers和product_categories表的方法以及执行INNER
SELECT的方法。两者有相同的时间。

这是EXPLAIN的链接：https :
//explain.depesz.com/s/9lhr

Postgres版本：

x86_64-unknown-linux-gnu上的PostgreSQL 9.4.5，由gcc（GCC）4.8.2 20140120（Red Hat
4.8.2-16）编译，64位

表和索引：

CREATE TABLE total_sales (
  id serial NOT NULL,
  value double precision,
  start_date date,
  end_date date,
  product_category_customer_id integer,
  created_at timestamp without time zone,
  updated_at timestamp without time zone,
  processed boolean,
  customer_id integer,
  product_category_id integer,
  period_id integer,
  CONSTRAINT total_sales_pkey PRIMARY KEY (id)
);
CREATE INDEX index_total_sales_on_customer_id ON total_sales (customer_id);
CREATE INDEX index_total_sales_on_period_id ON total_sales (period_id);
CREATE INDEX index_total_sales_on_product_category_customer_id ON total_sales (product_category_customer_id);
CREATE INDEX index_total_sales_on_product_category_id ON total_sales (product_category_id);
CREATE INDEX total_sales_product_category_period ON total_sales (product_category_id, period_id);
CREATE INDEX ts_pid_pcid_cid ON total_sales (period_id, product_category_id, customer_id);


CREATE TABLE performance_analyses (
  id serial NOT NULL,
  total_sales_id integer,
  status_id integer,
  created_at timestamp without time zone,
  updated_at timestamp without time zone,
  size double precision,
  period_size integer,
  nominal_variation double precision,
  percentual_variation double precision,
  relative_performance double precision,
  time_ago_max integer,
  deseasonalized_series text,
  significance character varying,
  relevance character varying,
  original_variation double precision,
  last_level double precision,
  quantiles text,
  range text,
  analysis_method character varying,
  CONSTRAINT performance_analyses_pkey PRIMARY KEY (id)
);
CREATE INDEX index_performance_analyses_on_status_id ON performance_analyses (status_id);
CREATE INDEX index_performance_analyses_on_total_sales_id ON performance_analyses (total_sales_id);


CREATE TABLE product_categories (
  id serial NOT NULL,
  name character varying,
  organization_id integer,
  created_at timestamp without time zone,
  updated_at timestamp without time zone,
  external_id character varying,
  CONSTRAINT product_categories_pkey PRIMARY KEY (id)
);
CREATE INDEX index_product_categories_on_organization_id ON product_categories (organization_id);


CREATE TABLE customers (
  id serial NOT NULL,
  name character varying,
  external_id character varying,
  region_id integer,
  organization_id integer,
  created_at timestamp without time zone,
  updated_at timestamp without time zone,
  active boolean DEFAULT false,
  visible boolean DEFAULT false,
  segment_id integer,
  "group" boolean,
  group_id integer,
  ticket_enabled boolean DEFAULT true,
  CONSTRAINT customers_pkey PRIMARY KEY (id)
);
CREATE INDEX index_customers_on_organization_id ON customers (organization_id);    
CREATE INDEX index_customers_on_region_id ON customers (region_id);
CREATE INDEX index_customers_on_segment_id ON customers (segment_id);

行数：

• customers-6,970行
• product_categories-34行
• performance_analyses-1,012,346行
• total_sales-7,104,441行

Answer 1

您的查询，已重写且100％等价：

SELECT count(*)
FROM   product_categories   pc 
JOIN   customers            c  USING (organization_id) 
JOIN   total_sales          ts ON ts.customer_id = c.id
JOIN   performance_analyses pa ON pa.total_sales_id = ts.id
WHERE  pc.organization_id = 3
AND    c.active  -- boolean can be used directly
AND    c.visible
AND    ts.product_category_id = pc.id
AND    ts.period_id = 193
AND    pa.size > 0;

另一个答案建议将所有条件移到FROM列表中的连接子句和顺序表中。这可能适用于具有相对原始查询计划程序的某些其他RDBMS。但是，尽管它对Postgres也没有害处，但它对查询的
性能 也 没有影响
-假设使用默认服务器配置。手册：

明确的内连接语法（INNER JOIN，CROSS JOIN，或古拙JOIN）在语义上的相同列出输入关系FROM，所以
不约束连接顺序 。

大胆强调我的。还有更多内容，请阅读手册。

关键设置为join_collapse_limit（默认为 8
）。无论您如何安排表格以及是否将条件写为WHERE或JOIN子句，Postgres查询计划器都会以它希望最快的方式重新安排您的4个表格。没什么区别。（对于某些其他类型的无法自由重排的联接，情况并非如此。）

重要的是，这些不同的连接可能性在语义上等效，但是执行成本可能大不相同。因此，计划者将探索所有这些因素，以找到最有效的查询计划。

最后，WHERE id IN (<subquery>)通常 不
等同于联接。对于右侧的重复匹配值，它不会在左侧乘以行。子查询的列在其余查询中不可见。联接可以将具有重复值的行相乘，并且可见列。
在这两种情况下，您的简单子查询都将挖掘一个唯一的列，因此在这种情况下没有有效的区别-除了IN (<subquery>)通常（至少有点）更慢且更冗长。使用联接。

您的查询

指标

product_categories 有34行。除非您计划添加更多表，否则索引不会对该表产生帮助。顺序扫描将始终更快。放下
index_product_categories_on_organization_id 。

customers 有6,970行。索引开始变得有意义。但是您的查询根据EXPLAIN输出使用了4,988个。只有对
索引 宽不到表的
索引进行仅索引扫描才有
帮助。假设WHERE active AND visible是常量谓词，我建议使用部分多列索引：

CREATE INDEX index_customers_on_organization_id ON customers (organization_id, id)
WHERE active AND visible;

我附加id了允许仅索引扫描。否则，该列在此查询的索引中无用。

total_sales 有7,104,441行。索引非常重要。我建议：

CREATE INDEX index_total_sales_on_product_category_customer_id
ON total_sales (period_id, product_category_id, customer_id, id)

再次，针对仅索引扫描。这是最重要的。

您可以删除完全冗余的索引 index_total_sales_on_product_category_id 。

performance_analyses 有1,012,346行。索引非常重要。我会建议另一个带有条件的部分索引size > 0：

CREATE INDEX index_performance_analyses_on_status_id
ON performance_analyses (total_sales_id)
WHERE pa.size > 0;

然而：

筛选器删除的行：0“

似乎这种情况毫无用处？是否有任何size > 0不正确的行？

创建这些索引后，您 需要 访问ANALYZE表。

表格统计

通常，我看到许多错误的估计。Postgres 低估 了几乎每一步返回的行数。我们看到的 嵌套循环
对于更少的行会更好。除非这是不太可能的巧合，否则表统计信息将严重过时。您需要访问自动真空设置，还可能需要访问两个大表performance_analyses和的每表设置
total_sales。

你已经没有运行VACUUM和ANALYZE，这让查询更慢。那没有多大意义。我将VACUUM FULL在这两个表上运行一次（如果您能负担得起独占锁）。否则尝试pg_repack。
考虑到所有的统计数据和糟糕的计划，我会考虑vacuumdb -fz yourdb在您的数据库上运行一个完整的数据库。那会在原始条件下重写所有表和索引，但是定期使用是不好的。这也很昂贵，并且将锁定您的数据库更长的时间！