我有一张大桌子,我想计算一些子集的数量。过滤器参数位于具有索引的列上。如果我只是写一个普通的查询,我会这样做:
SELECT count(*) FROM foo WHERE ts > '2022-07-01';
但是,可以将属性符号与count
函数一起使用:
SELECT foo.count FROM foo WHERE ts > '2022-07-01';
我的理解是第二个版本在语义上应该与第一个版本相同。然而,在实践中,它会导致不同的查询计划,其性能特征明显更差(parallel index-only scan
vs parallel seq scan
)。
不幸的是,我被锁定在第二个版本中,因为这是PostgREST实现count
查询的方式。这种情况实际上比这个玩具示例更糟糕,因为在大多数情况下我是针对视图而不是表进行选择,并且在功能(“正常”)情况下,查询计划器足够聪明以使用索引,而在属性案例,看起来规划器执行了整个视图(例如,与执行大致相同的性能SELECT * FROM myview WHERE ts > '2022-07-01'
),然后只对结果集进行计数。在某些列派生自成本适中的内联函数调用的情况下,这是在 2.5 秒和 2.5 分钟内运行的查询之间的差异。
我感兴趣的是
- 理解为什么这两个看似等价的查询会产生不同的查询计划,以及
- 如果有一种方法可以促使查询计划器对查询的属性标记形式使用更优化的计划。
最小复制:
CREATE TABLE bar (id PRIMARY KEY)
CREATE TABLE foo (
id bigint PRIMARY KEY,
ts timestamptz UNIQUE,
-- for whatever reason I can only get a replication if I add a foreign key.
-- Not sure if this is significant...
bar_id bigint REFERENCES bar(id)
);
INSERT INTO bar (id) SELECT generate_series(1, 1e6);
WITH t AS (
select *
from generate_series('2022-01-01 00:00'::timestamptz, '2022-12-31 23:59:59', '1 second') ts
)
INSERT INTO foo (id, ts, bar_id)
SELECT rank, ts, CASE WHEN rank <= 1e6 THEN rank ELSE NULL END
FROM (SELECT rank() over (order by ts), ts FROM t) a
;
EXPLAIN ANALYZE SELECT count(*) FROM foo WHERE ts > '2022-07-01';
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Finalize Aggregate (cost=587875.34..587875.35 rows=1 width=8) (actual time=4789.976..4802.692 rows=1 loops=1) │
│ -> Gather (cost=587875.13..587875.34 rows=2 width=8) (actual time=4789.778..4802.645 rows=3 loops=1) │
│ Workers Planned: 2 │
│ Workers Launched: 2 │
│ -> Partial Aggregate (cost=586875.13..586875.14 rows=1 width=8) (actual time=4740.529..4740.530 rows=1 loops=3) │
│ -> Parallel Index Only Scan using foo_ts_key on foo (cost=0.56..563750.57 rows=9249822 width=0) (actual time=0.856..4503.148 rows=5212800 loops=3) │
│ Index Cond: (ts < '2022-07-01 00:00:00+00'::timestamp with time zone) │
│ Heap Fetches: 24110439 │
│ Planning Time: 0.344 ms │
│ JIT: │
│ Functions: 11 │
│ Options: Inlining true, Optimization true, Expressions true, Deforming true │
│ Timing: Generation 3.898 ms, Inlining 77.703 ms, Optimization 30.218 ms, Emission 20.267 ms, Total 132.086 ms │
│ Execution Time: 4806.155 ms │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
SELECT foo.count FROM foo WHERE ts > '2022-07-01';
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Finalize Aggregate (cost=721287.60..721287.61 rows=1 width=8) (actual time=2051.126..2059.868 rows=1 loops=1) │
│ -> Gather (cost=721287.38..721287.59 rows=2 width=8) (actual time=2051.004..2059.858 rows=3 loops=1) │
│ Workers Planned: 2 │
│ Workers Launched: 2 │
│ -> Partial Aggregate (cost=720287.38..720287.39 rows=1 width=8) (actual time=2020.404..2020.405 rows=1 loops=3) │
│ -> Parallel Seq Scan on foo (cost=0.00..697162.83 rows=9249822 width=81) (actual time=49.001..1761.162 rows=5212800 loops=3) │
│ Filter: (ts < '2022-07-01 00:00:00+00'::timestamp with time zone) │
│ Rows Removed by Filter: 5299200 │
│ Planning Time: 0.403 ms │
│ JIT: │
│ Functions: 17 │
│ Options: Inlining true, Optimization true, Expressions true, Deforming true │
│ Timing: Generation 2.818 ms, Inlining 76.615 ms, Optimization 38.902 ms, Emission 30.954 ms, Total 149.289 ms │
│ Execution Time: 2061.402 ms │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
请注意,在这种最小复制中,seq 扫描示例的执行时间实际上更好,但它表明可以生成不同的查询计划,当我在真实数据库上尝试时,基于索引的计划往往要好得多.
该文档描述了这一点
foo.count
并且count(foo)
实际上是相同的:观察到的差异只是 和 之间的
count(*)
差异count(foo)
。他们是不同的。您可能被列表*
中“所有列”的缩写误导了SELECT
。但是*
in aggregate functions 是不同的:它是 SQL standard's quirky way ow writing an aggregate function call without parameters。比较此 PostgreSQL 错误消息:While
count(*)
计算行数,count(foo)
计算其中foo IS NOT NULL
的行数。所以后者不能使用仅索引扫描,因为它必须检索行并检查它是否为 NULL。