时间戳

首页 > 解决方案 > 具有多列索引的 postgres 查询的执行时间很慢

postgresql - 具有多列索引的 postgres 查询的执行时间很慢

问题描述

我们在 Amazon RDS 上运行 PostgresSql 9.6.11 数据库。其中一个查询的执行时间是 6633.645 毫秒。这似乎很慢。我可以进行哪些更改来改进此查询的执行时间。

该查询正在选择 3 列，其中数据与 6 列匹配。

select
    platform,
    publisher_platform,
    adset_id
FROM "adsets"
WHERE
    (("adsets"."account_id" IN ('1595321963838425', '1320001405', 'urn:li:sponsoredAccount:507697540')) AND
    ("adsets"."date" >= '2019-05-06 00:00:00.000000+0000') AND ("adsets"."date" <= '2019-05-13 23:59:59.999999+0000'))
GROUP BY
    "adsets"."platform",
    "adsets"."publisher_platform",
    "adsets"."adset_id"
ORDER BY
    "adsets"."platform",
    "adsets"."publisher_platform",
    "adsets"."adset_id";

该查询基于名为 adset 表的表。该表具有以下列

account_id | 文字
campaign_id | 文字
adset_id | 文字
名称 | 文本
日期 | 没有时区的时间戳 publisher_platform | 文本

和其他 15 列，它们是整数和文本字段的混合。

我们添加了以下索引 -

  1. “adsets_composite_unique_key” 唯一约束，btree（平台，account_id，campaign_id，adset_id，日期，publisher_platform）
  2. "adsets_account_id_date_idx" btree (account_id DESC, date DESC) CLUSTER
  3. “adsets_account_id_index” btree (account_id)
  4. "adsets_adset_id_index" btree (adset_id)
  5. "adsets_campaign_id_index" btree (campaign_id)
  6. “adsets_name_index” btree（名称）
  7. "adsets_platform_platform_id_publisher_platform" btree (account_id, platform, publisher_platform, adset_id)
  8. "idx_account_date_adsets" btree (account_id, 日期)
  9. “platform_pub_index” btree（平台、publisher_platform、adset_id）。

postgres 上的 work_mem 已设置为 125MB

解释（分析）显示

   Group  (cost=33447.55..33532.22 rows=8437 width=29) (actual time=6625.170..6633.062 rows=2807 loops=1)
   Group Key: platform, publisher_platform, adset_id
   ->  Sort  (cost=33447.55..33468.72 rows=8467 width=29) (actual time=6625.168..6629.271 rows=22331 loops=1)
         Sort Key: platform, publisher_platform, adset_id
         Sort Method: quicksort  Memory: 2513kB
         ->  Bitmap Heap Scan on adsets  (cost=433.63..32895.18 rows=8467 width=29) (actual time=40.003..6471.898 rows=22331 loops=1)
               Recheck Cond: ((account_id = ANY ('{1595321963838425,1320001405,urn:li:sponsoredAccount:507697540}'::text[])) AND (date >= '2019-05-06 00:00:00'::timestamp without time zone) AND (date <= '
2019-05-13 23:59:59.999999'::timestamp without time zone))
               Heap Blocks: exact=52907
               ->  Bitmap Index Scan on idx_account_date_adsets  (cost=0.00..431.51 rows=8467 width=0) (actual time=27.335..27.335 rows=75102 loops=1)
                     Index Cond: ((account_id = ANY ('{1595321963838425,1320001405,urn:li:sponsoredAccount:507697540}'::text[])) AND (date >= '2019-05-06 00:00:00'::timestamp without time zone) AND (date
<= '2019-05-13 23:59:59.999999'::timestamp without time zone))
 Planning time: 5.380 ms
 Execution time: 6633.645 ms
(12 rows)

解释 depesz

标签： postgresqlpostgresql-performance

解决方案

首先，您在使用GROUP BY时没有实际选择任何聚合。你也可以SELECT DISTINCT在你的查询中做。除此之外，这是您可能应该使用的 B 树索引：

CREATE INDEX idx ON adsets (account_id, date, platform, publisher_platform,
    adset_id);

当前索引的问题在于，虽然它确实涵盖了您选择的列，但它不涉及出现在WHERE子句中的列。这意味着 Postgres 可能选择不使用索引，而只是扫描整个表。

请注意，我的建议仍然无法处理查询的 select distinct 部分，但至少它可能会加快查询该部分之前的所有内容。

这是您更新的查询：

SELECT DISTINCT
    platform,
    publisher_platform,
    adset_id
FROM adsets
WHERE
    account_id IN ('1595321963838425', '1320001405',
                   'urn:li:sponsoredAccount:507697540') AND
    date >= '2019-05-06' AND date < '2019-05-14';

推荐阅读