对有条件的大表的连接的计数很慢

Question

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;

我尝试了内部联接customers和product_categories表的方法，并进行了内部选择。两人都有同样的时间。

以下是解释链接:https://explain.depesz.com/s/9lhr

Postgres版本：

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);

null

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列表中的join子句和order表中。这可能适用于具有相对原始查询规划器的某些其他RDBMS。但是，虽然它对Postgres也没有影响，但它对查询的性能也没有影响--假设是默认的服务器配置。手册：

显式内部联接语法(内部联接、交叉联接或未经修饰的联接)在语义上与从中列出中的输入关系相同，因此不限制联接顺序。

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

重要的一点是，这些不同的联接可能会给出语义上等价的结果，但可能有非常不同的执行成本。因此，计划器将探索所有这些计划，试图找到最有效的查询计划。

相关的：

null

Customers有6,970行。索引开始有意义了。但是根据explain输出，您的查询使用了其中的4,988个。只有对比表宽得多的索引进行仅索引扫描才能有所帮助。假设其中active和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)

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

筛选器删除的行：0“

好像这个条件没有用？是否有大小>0不为真的行？

创建这些索引后，需要分析表。

null