Charlie Clark's questions

我认为这是一个相当标准的任务类型，但我还没有看到任何详细介绍性能的内容，尽管比较星期几和小时的日期时间索引优化非常接近。我想通过我的温度传感器计算一年中每个月的平均温度。

我正在使用 Postgres 15

该表如下所示：

CREATE TABLE public.hygrometer (
    device_id bpchar(2) NOT NULL,
    temperature numeric(3, 1) NOT NULL,
    "timestamp" timestamp NOT NULL
);
CREATE INDEX time_id ON public.hygrometer (device_id bpchar_ops);

这是我第一次使用窗口函数，这是我的查询：

select distinct ON (month) 
date_part('month', "timestamp") as month, 
avg(temperature) over (PARTITION by date_part('month', "timestamp") ) as avg_temp
from hygrometer
order by month

我的一个问题是是否可以避免date_part('month', "timestamp")在查询中重复输入。我怀疑这是因为 select 中表达式的范围与窗口函数中的不同。

最初，我创建的数据粒度为 7 分钟（75,000 行），然后是 1 分钟（525,600 行），然后是 1 秒（3000 万行）。

我创建了以下部分索引。

CREATE INDEX month_idx ON public.hygrometer USING btree (date_part('month'::text, "timestamp"));

在 7 分钟粒度下，添加索引可以忽略不计，在 1 分钟粒度下，它会变得明显，尽管使用DISTINCT ON会带来最大的提升。

然而，令我惊讶的是，在没有索引的情况下，最大数据集的性能稍快一些，因为规划器开始并行工作。我读过，这应该可以通过索引实现，但即使将并行成本降低到接近 0，也无法说服服务器这样做。

这是带有索引的大表的查询计划：

 Unique  (cost=0.56..1725911.36 rows=12 width=40)
   Output: (date_part('month'::text, "timestamp")), (avg(temperature) OVER (?))
   ->  WindowAgg  (cost=0.56..1647069.04 rows=31536928 width=40)
         Output: (date_part('month'::text, "timestamp")), avg(temperature) OVER (?)
         ->  Index Scan using month_idx on public.hygrometer  (cost=0.56..1095172.80 rows=31536928 width=14)
               Output: date_part('month'::text, "timestamp"), temperature

这是删除了索引的规划器：

 Unique  (cost=2364029.66..6667763.19 rows=31536928 width=40)
   Output: (date_part('month'::text, "timestamp")), (avg(temperature) OVER (?))
   ->  WindowAgg  (cost=2364029.66..6588920.87 rows=31536928 width=40)
         Output: (date_part('month'::text, "timestamp")), avg(temperature) OVER (?)
         ->  Gather Merge  (cost=2364029.66..6037024.63 rows=31536928 width=14)
               Output: (date_part('month'::text, "timestamp")), temperature
               Workers Planned: 2
               ->  Sort  (cost=2363979.63..2396830.60 rows=13140387 width=14)
                     Output: (date_part('month'::text, "timestamp")), temperature
                     Sort Key: (date_part('month'::text, hygrometer."timestamp"))
                     ->  Parallel Seq Scan on public.hygrometer  (cost=0.00..361151.83 rows=13140387 width=14)
                           Output: date_part('month'::text, "timestamp"), temperature

我希望这里可以进行并行索引扫描。我想知道不同类型的索引（哈希？）是否更有意义。还有什么可能？大概是使用包含月份的物化视图。感谢您的意见和建议。

更新

看起来哈希索引是错误的策略，将会被忽略。

正如@jjanes 指出的：这里不需要使用窗口函数，因为这可以通过简单的聚合来实现。

这是查询：

select  
date_part('month', "timestamp") as month, 
avg(temperature)
from hygrometer
group by month
order by month

这就是计划：

 Finalize GroupAggregate  (cost=427831.20..427834.36 rows=12 width=40)
   Output: (date_part('month'::text, "timestamp")), avg(temperature)
   Group Key: (date_part('month'::text, hygrometer."timestamp"))
   ->  Gather Merge  (cost=427831.20..427834.00 rows=24 width=40)
         Output: (date_part('month'::text, "timestamp")), (PARTIAL avg(temperature))
         Workers Planned: 2
         ->  Sort  (cost=426831.18..426831.21 rows=12 width=40)
               Output: (date_part('month'::text, "timestamp")), (PARTIAL avg(temperature))
               Sort Key: (date_part('month'::text, hygrometer."timestamp"))
               ->  Partial HashAggregate  (cost=426830.78..426830.96 rows=12 width=40)
                     Output: (date_part('month'::text, "timestamp")), PARTIAL avg(temperature)
                     Group Key: date_part('month'::text, hygrometer."timestamp")
                     ->  Parallel Seq Scan on public.hygrometer  (cost=0.00..361134.27 rows=13139302 width=14)
                           Output: date_part('month'::text, "timestamp"), temperature

索引的使用并不是立即显而易见的，但删除它的速度较慢，并且计划有所不同：

 Finalize GroupAggregate  (cost=2364783.98..6363725.60 rows=31534324 width=40)
   Output: (date_part('month'::text, "timestamp")), avg(temperature)
   Group Key: (date_part('month'::text, hygrometer."timestamp"))
   ->  Gather Merge  (cost=2364783.98..5693621.21 rows=26278604 width=40)
         Output: (date_part('month'::text, "timestamp")), (PARTIAL avg(temperature))
         Workers Planned: 2
         ->  Partial GroupAggregate  (cost=2363783.96..2659418.25 rows=13139302 width=40)
               Output: (date_part('month'::text, "timestamp")), PARTIAL avg(temperature)
               Group Key: (date_part('month'::text, hygrometer."timestamp"))
               ->  Sort  (cost=2363783.96..2396632.21 rows=13139302 width=14)
                     Output: (date_part('month'::text, "timestamp")), temperature
                     Sort Key: (date_part('month'::text, hygrometer."timestamp"))
                     ->  Parallel Seq Scan on public.hygrometer  (cost=0.00..361134.27 rows=13139302 width=14)
                           Output: date_part('month'::text, "timestamp"), temperature

您可以看到它在分组和排序时用于减少样本大小。实时性提高了 3-4 倍，并且索引构建速度非常快。对于这种改进来说，大约是桌子大小的三分之一是合理的。

我定期从 httparchive.org 导入数据。数据是一个 MySQL CSV 导出，我使用 pgloader，它处理这个导出的怪癖（\Nfor NULL）等。我还需要做一些额外的处理以进行规范化：

• 在协议 (http|https) 和主机部分中拆分 url
• 将字符串日期“Mon DD YYYY”转换为日期对象

目前，我在导入数据时有一些触发器可以执行此操作，但我正在寻找改进方法，特别是查看是否可以并行运行某些步骤。

我有以下用于提取协议和端口的 CTE：

with split as
(select regexp_match(url, '(https|http)://(.+)/' )as parts 
from urls )

在本地运行这似乎比tsdebug

这适用于选择，但作为更新似乎非常慢。

with split as
(select regexp_match(url, '(https|http)://(.+)/' )as parts 
from urls )
update urls
set 
protocol = parts[1],
host = parts[2] 
from split

另一种方法，尤其是在处理文本源时，会在 URL 进入 Postgres 之前对其进行拆分。

未压缩的 CSV 为 3526430884 字节，导入大约需要 20 分钟，无需处理。但这与加工相比是两倍多。FWIW 我也尝试过使用外部数据包装器。但是，即使在使用 CSV（空值、编码）解决了各种问题之后，这也会导致内存错误。

在一些帮助下，我设法运行了基准测试并改进了我的触发器。

CREATE OR REPLACE FUNCTION public.extract_protocol()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE elements text [];
BEGIN
elements := regexp_match(NEW.url, '(https|http)://(.+)/');
NEW.protocol = elements[1];
NEW.host = elements[2];
RETURN NEW;
END;
$function$

现在，这比进行后续更新运行得更快，但两者都不是限制因素。现在的瓶颈是在将清理后的数据插入主表时索引的开销。我认为我唯一的选择是权衡插入索引的成本，而不是禁用然后添加它们。

我有一个 Postgres 数据库，其中包含有关网站的信息和一个名为sitescolumn的表host。这已经有一个文本模式操作的索引，非常适合从域名的开头进行搜索：WHERE host LIKE 'www.bran%但对于部分匹配（例如WHERE host LIKE '%.bran%'导致扫描的部分匹配）来说不是很好。目前大约有 750 万台主机，因此搜索性能已经很明显了。

根据下面的建议，我添加了一个 trigrm 索引（您需要启用扩展才能执行此操作：

create extension pg_trgm;

然后添加一个合适的索引：

CREATE INDEX trgm_idx ON sites USING GIST (host gist_trgm_ops);

与所有索引一样，这可能需要一些时间，具体取决于数据库的大小。

在索引之前，我的查询大约需要 180 秒才能在 750 万个中找到 200 个项目，但这因相关子查询而变得复杂，这似乎使问题复杂化并主导查询，并建议我应该首先考虑优化其他内容，特别是日期索引。

所以比较的结果是：使用 text_ops 索引大约需要 2s，使用 trigram 大约需要 7。我想如果我创建一个只包含主机名相关部分的索引，我可能会更幸运。

我有一个数据库，其中包含一些错误的结果，这些结果本质上是重复的，但在技术上并不重复。结构是这样的：

id_page (PK), id_site, label, create_date

每个标签的每个站点应该只有一个条目，但事实证明我有一些重复的site和label，其中id_page和create_date是不同的。我想删除最低的行create_date。

我想我已经想出了一个解决方案，但我会很感激反馈。

WITH duplicates as (
   SELECT id_page, id_site, count(id_site) over (partition by id_site) as ct, 
    min("create_date") over (partition by id_site) as dt
   from pages
   where label = '2018-12-15'
   )
DELETE from pages
where id_page in (
   select p.id_page
   from duplicates as d
   join pages as p on (p.id_page = d.id_page
                   and p.create_date = d.dt)
WHERE ct = 2
);

我有一个包含大约 1000 万行的表和一个日期字段的索引。当我尝试提取索引字段的唯一值时，即使结果集只有 26 个项目，Postgres 也会运行顺序扫描。为什么优化师选择这个计划？我能做些什么来避免它？

从其他答案中，我怀疑这与查询和索引一样多。

explain select "labelDate" from pages group by "labelDate";
                              QUERY PLAN
-----------------------------------------------------------------------
 HashAggregate  (cost=524616.78..524617.04 rows=26 width=4)
   Group Key: "labelDate"
   ->  Seq Scan on pages  (cost=0.00..499082.42 rows=10213742 width=4)
(3 rows)

表结构：

http=# \d pages
                                       Table "public.pages"
     Column      |          Type          |        Modifiers
-----------------+------------------------+----------------------------------
 pageid          | integer                | not null default nextval('...
 createDate      | integer                | not null
 archive         | character varying(16)  | not null
 label           | character varying(32)  | not null
 wptid           | character varying(64)  | not null
 wptrun          | integer                | not null
 url             | text                   |
 urlShort        | character varying(255) |
 startedDateTime | integer                |
 renderStart     | integer                |
 onContentLoaded | integer                |
 onLoad          | integer                |
 PageSpeed       | integer                |
 rank            | integer                |
 reqTotal        | integer                | not null
 reqHTML         | integer                | not null
 reqJS           | integer                | not null
 reqCSS          | integer                | not null
 reqImg          | integer                | not null
 reqFlash        | integer                | not null
 reqJSON         | integer                | not null
 reqOther        | integer                | not null
 bytesTotal      | integer                | not null
 bytesHTML       | integer                | not null
 bytesJS         | integer                | not null
 bytesCSS        | integer                | not null
 bytesHTML       | integer                | not null
 bytesJS         | integer                | not null
 bytesCSS        | integer                | not null
 bytesImg        | integer                | not null
 bytesFlash      | integer                | not null
 bytesJSON       | integer                | not null
 bytesOther      | integer                | not null
 numDomains      | integer                | not null
 labelDate       | date                   |
 TTFB            | integer                |
 reqGIF          | smallint               | not null
 reqJPG          | smallint               | not null
 reqPNG          | smallint               | not null
 reqFont         | smallint               | not null
 bytesGIF        | integer                | not null
 bytesJPG        | integer                | not null
 bytesPNG        | integer                | not null
 bytesFont       | integer                | not null
 maxageMore      | smallint               | not null
 maxage365       | smallint               | not null
 maxage30        | smallint               | not null
 maxage1         | smallint               | not null
 maxage0         | smallint               | not null
 maxageNull      | smallint               | not null
 numDomElements  | integer                | not null
 numCompressed   | smallint               | not null
 numHTTPS        | smallint               | not null
 numGlibs        | smallint               | not null
 numErrors       | smallint               | not null
 numRedirects    | smallint               | not null
 maxDomainReqs   | smallint               | not null
 bytesHTMLDoc    | integer                | not null
 maxage365       | smallint               | not null
 maxage30        | smallint               | not null
 maxage1         | smallint               | not null
 maxage0         | smallint               | not null
 maxageNull      | smallint               | not null
 numDomElements  | integer                | not null
 numCompressed   | smallint               | not null
 numHTTPS        | smallint               | not null
 numGlibs        | smallint               | not null
 numErrors       | smallint               | not null
 numRedirects    | smallint               | not null
 maxDomainReqs   | smallint               | not null
 bytesHTMLDoc    | integer                | not null
 fullyLoaded     | integer                |
 cdn             | character varying(64)  |
 SpeedIndex      | integer                |
 visualComplete  | integer                |
 gzipTotal       | integer                | not null
 gzipSavings     | integer                | not null
 siteid          | numeric                |
Indexes:
    "pages_pkey" PRIMARY KEY, btree (pageid)
    "pages_date_url" UNIQUE CONSTRAINT, btree ("urlShort", "labelDate")
    "idx_pages_cdn" btree (cdn)
    "idx_pages_labeldate" btree ("labelDate") CLUSTER
    "idx_pages_urlshort" btree ("urlShort")
Triggers:
    pages_label_date BEFORE INSERT OR UPDATE ON pages
      FOR EACH ROW EXECUTE PROCEDURE fix_label_date()