如何让 date_part 查询命中索引？

2023-12-02

我还没有能够让这个查询命中索引而不是执行完整扫描 - 我有另一个查询，它对几乎相同的表使用 date_part('day', datelocal) （该表的数据稍微少一些，但是相同的结构），并且将命中我在 datelocal 列上创建的索引（这是一个没有时区的时间戳）。查询（此查询对表执行并行 seq 扫描并执行内存快速排序）：

SELECT
    date_part('hour', datelocal) AS hour,
    SUM(CASE WHEN gender LIKE 'male' THEN views ELSE 0 END) AS male,
    SUM(CASE WHEN gender LIKE 'female' THEN views ELSE 0 END) AS female
FROM reportimpression
WHERE datelocal >= '2-1-2019' AND datelocal < '2-28-2019'
GROUP BY date_part('hour', datelocal)
ORDER BY date_part('hour', datelocal)

这是确实命中我的日期本地索引的另一项：

SELECT
    date_part('day', datelocal) AS day,
    SUM(CASE WHEN gender LIKE 'male' THEN views ELSE 0 END) AS male,
    SUM(CASE WHEN gender LIKE 'female' THEN views ELSE 0 END) AS female
FROM reportimpressionday
WHERE datelocal >= '2-1-2019' AND datelocal < '2-28-2019'
GROUP BY date_trunc('day', datelocal), date_part('day', datelocal)
ORDER BY date_trunc('day', datelocal)

为这件事让我头疼！关于如何加快第一个或至少使其达到索引的任何想法？我尝试在 datelocal 字段上创建索引，在 datelocal、性别和视图上创建复合索引，以及在 date_part('hour', datelocal) 上创建表达式索引，但这些都不起作用。

Schemas:

-- Table Definition ----------------------------------------------

CREATE TABLE reportimpression (
    datelocal timestamp without time zone,
    devicename text,
    network text,
    sitecode text,
    advertisername text,
    mediafilename text,
    gender text,
    agegroup text,
    views integer,
    impressions integer,
    dwelltime numeric
);

-- Indices -------------------------------------------------------

CREATE INDEX reportimpression_datelocal_index ON reportimpression(datelocal timestamp_ops);
CREATE INDEX reportimpression_viewership_index ON reportimpression(datelocal timestamp_ops,views int4_ops,impressions int4_ops,gender text_ops,agegroup text_ops);
CREATE INDEX reportimpression_test_index ON reportimpression(datelocal timestamp_ops,(date_part('hour'::text, datelocal)) float8_ops);

-- Table Definition ----------------------------------------------

CREATE TABLE reportimpressionday (
    datelocal timestamp without time zone,
    devicename text,
    network text,
    sitecode text,
    advertisername text,
    mediafilename text,
    gender text,
    agegroup text,
    views integer,
    impressions integer,
    dwelltime numeric
);

-- Indices -------------------------------------------------------

CREATE INDEX reportimpressionday_datelocal_index ON reportimpressionday(datelocal timestamp_ops);
CREATE INDEX reportimpressionday_detail_index ON reportimpressionday(datelocal timestamp_ops,views int4_ops,impressions int4_ops,gender text_ops,agegroup text_ops);

解释（分析、缓冲）输出：

Finalize GroupAggregate  (cost=999842.42..999859.67 rows=3137 width=24) (actual time=43754.700..43754.714 rows=24 loops=1)
  Group Key: (date_part('hour'::text, datelocal))
  Buffers: shared hit=123912 read=823290
  I/O Timings: read=81228.280
  ->  Sort  (cost=999842.42..999843.99 rows=3137 width=24) (actual time=43754.695..43754.698 rows=48 loops=1)
        Sort Key: (date_part('hour'::text, datelocal))
        Sort Method: quicksort  Memory: 28kB
        Buffers: shared hit=123912 read=823290
        I/O Timings: read=81228.280
        ->  Gather  (cost=999481.30..999805.98 rows=3137 width=24) (actual time=43754.520..43777.558 rows=48 loops=1)
              Workers Planned: 1
              Workers Launched: 1
              Buffers: shared hit=123912 read=823290
              I/O Timings: read=81228.280
              ->  Partial HashAggregate  (cost=998481.30..998492.28 rows=3137 width=24) (actual time=43751.649..43751.672 rows=24 loops=2)
                    Group Key: date_part('hour'::text, datelocal)
                    Buffers: shared hit=123912 read=823290
                    I/O Timings: read=81228.280
                    ->  Parallel Seq Scan on reportimpression  (cost=0.00..991555.98 rows=2770129 width=17) (actual time=13.097..42974.126 rows=2338145 loops=2)
                          Filter: ((datelocal >= '2019-02-01 00:00:00'::timestamp without time zone) AND (datelocal < '2019-02-28 00:00:00'::timestamp without time zone))
                          Rows Removed by Filter: 6792750
                          Buffers: shared hit=123912 read=823290
                          I/O Timings: read=81228.280
Planning time: 0.185 ms
Execution time: 43777.701 ms

好吧，您的两个查询都在不同的表上（reportimpression vs. reportimpressionday），所以两个查询的比较实际上不是比较。你是否ANALYZE两个都？各种列统计数据也可能发挥作用。索引或表膨胀可能不同。所有行中的大部分是否符合 2019 年 2 月的条件？ ETC。

盲目一试，比较两个表的百分比：

SELECT tbl, round(share * 100 / total, 2) As percentage
FROM  (
   SELECT text 'reportimpression' AS tbl
        , count(*)::numeric AS total
        , count(*) FILTER (WHERE datelocal >= '2019-02-01' AND datelocal < '2019-03-01')::numeric AS share
   FROM  reportimpression

   UNION ALL
   SELECT 'reportimpressionday'
        , count(*)
        , count(*) FILTER (WHERE datelocal >= '2019-02-01' AND datelocal < '2019-03-01')
   FROM  reportimpressionday
  ) sub;

是为了reportimpression大？那么它可能会超出索引预计可以帮助的数量。

一般来说，你的索引reportimpression_datelocal_indexon (datelocal) 看起来不错，并且reportimpression_viewership_index如果 autovacuum 击败了表上的写入负载，甚至允许仅索引扫描。（尽管impressions & agegroup对于这个来说只是死运费，如果没有的话它会工作得更好）。

Answer

You got 26.6 percent, and day is 26.4 percent对于我的查询。对于如此大的百分比，索引通常没有用处at all。顺序扫描通常是最快的方法。仅进行仅索引扫描may如果基础表更大的话仍然有意义。（或者你有severe表膨胀，索引膨胀更少，这使得索引再次变得更有吸引力。）

您的第一个查询可能刚刚跨越临界点。尝试缩小时间范围，直到看到仅索引扫描。您不会看到超过大约 5% 的所有行符合资格的（位图）索引扫描（取决于许多因素）。

Queries

尽管如此，请考虑这些修改后的查询：

SELECT date_part('hour', datelocal)                AS hour
     , SUM(views) FILTER (WHERE gender = 'male')   AS male
     , SUM(views) FILTER (WHERE gender = 'female') AS female
FROM   reportimpression
WHERE  datelocal >= '2019-02-01'
AND    datelocal <  '2019-03-01' -- '2019-02-28'  -- ?
GROUP  BY 1
ORDER  BY 1;

SELECT date_trunc('day', datelocal)                AS day
     , SUM(views) FILTER (WHERE gender = 'male')   AS male
     , SUM(views) FILTER (WHERE gender = 'female') AS female
FROM   reportimpressionday
WHERE  datelocal >= '2019-02-01'
AND    datelocal <  '2019-03-01'
GROUP  BY 1
ORDER  BY 1;

主要观点

使用本地化时日期格式 like '2-1-2019'，经过to_timestamp()具有明确的格式说明符。否则，这取决于区域设置，并且当从具有不同设置的会话调用时可能会（静默）中断。而是使用 ISO 日期/时间格式（如图所示），该格式不依赖于区域设置。
看起来您想包括整整一个月二月。但您的查询错过了上限。例如，二月可能有 29 天。一个datelocal < '2-28-2019'也不包括 2 月 28 日的所有时间。使用datelocal < '2019-03-01'反而。
比较便宜的是按相同表达式进行分组和排序正如你在SELECT如果可以的话列出来。所以使用date_trunc()那里也有。没有必要时不要使用不同的表达方式。如果你need结果中的日期部分，将其应用于分组表达式，例如：
```
SELECT date_part('day', date_trunc('day', datelocal)) AS day
...
GROUP  BY date_trunc('day', datelocal)
ORDER  BY date_trunc('day', datelocal);
```
代码噪音更大，但速度更快（并且可能也更容易针对查询规划器进行优化）。
Use the 总计的FILTER clause在 Postgres 9.4 或更高版本中。它更干净，速度更快一些。看：
- 如何简化这个游戏统计查询？
- 对于绝对性能，SUM 更快还是 COUNT 更快？

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)