sysdate() 会导致 Postgres 忽略索引并执行代价高昂的顺序扫描

Question

>有人遇到过这种情况吗？Postgres Enterprise DB Advanced Server 11.5.12

sysdate()(Oracle 专有(导致 Seq 扫描，在本例中为 4,782 行：

EXPLAIN SELECT p.id, p.practice
FROM PatientStatistics ps
INNER JOIN Patients p
ON p.id=ps.patient
WHERE ps.nextfutureapptdateservertime <= sysdate()
ORDER BY p.id ASC;
Hash Join  (cost=799.81..1761.53 rows=4782 width=8)
Hash Cond: (p.id = ps.patient)
->  Index Only Scan using patients_index3 on patients p  (cost=0.29..921.44 rows=15442 width=8)
->  Hash  (cost=644.11..644.11 rows=4782 width=4)
->  Seq Scan on patientstatistics ps  (cost=0.00..644.11 rows=4782 width=4)
Filter: (nextfutureapptdateservertime <= sysdate)

更改为now()或current_timestamp(SQL 标准(可解决此问题。Postgres正确使用了索引：

EXPLAIN SELECT p.id, p.practice
FROM PatientStatistics ps
INNER JOIN Patients p
ON p.id=ps.patient
WHERE ps.nextfutureapptdateservertime <= now()
ORDER BY p.id ASC;
Nested Loop  (cost=0.57..51.41 rows=17 width=8)
->  Index Only Scan using "patientstatisti_idx$$_0c9a0048" on patientstatistics ps  (cost=0.29..8.53 rows=17 width=4)
Index Cond: (nextfutureapptdateservertime <= now())
->  Index Scan using patients_pk on patients p  (cost=0.29..2.52 rows=1 width=8)
Index Cond: (id = ps.patient)

有趣的是，请注意这些函数的输出不同：

SELECT now();
SELECT current_timestamp;
15-JAN-20 09:36:41.932741 -05:00
15-JAN-20 09:36:41.932930 -05:00
SELECT sysdate();
15-JAN-20 09:37:17

也许Postgres的日期索引是使用具有十进制部分的日期时间进行哈希处理的。计划人员看到它传递的日期没有十进制，并且知道索引的键不会准确排列，因此它会回退到扫描以确保查询提供 100% 准确的结果。

经过30 分钟的谷歌搜索后，我在网上找不到任何关于这个的信息。

Answer 1

我不知道EDB的专有分支，所以以下内容是基于猜测。

now()或(等效地(current_timestamp是一个STABLE函数，因此如果在语句执行(实际上是事务(过程中多次计算它，它将返回相同的值。

怀疑sysdate，就像PostgreSQL的clock_timestamp()一样，是VOLATILE(返回实际时间(。

然后，每次与行进行比较时，该函数都可以具有不同的值，这使得无法使用索引扫描。

如果我的怀疑不正确，我会称之为EDB错误。

Answer 2

我不知道他们是如何实现的，但是此解决方法在此处正常运行：

---

CREATE OR REPLACE FUNCTION mysysdate(OUT timestamptz)
AS
$func$
select now();
$func$
language sql stable;
select mysysdate() ;
EXPLAIN select *
FROM public.feature_timeslice
WHERE valid_time_begin < mysysdate() - '10 year + 14 days'::interval;
select version() ;
df+ mysysdate

---

输出：

---

CREATE FUNCTION
mysysdate           
-------------------------------
2020-01-15 17:15:13.896497+01
(1 row)
QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
Index Scan using feature_timeslice_alt2 on feature_timeslice  (cost=0.42..4474.84 rows=9206 width=28)
Index Cond: (valid_time_begin < (now() - '10 years 14 days'::interval))
(2 rows)
version                                                
-------------------------------------------------------------------------------------------------------
PostgreSQL 11.3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.4) 4.8.4, 64-bit
(1 row)
                 List of functions
Schema |   Name    |     Result data type     |     Argument data types      | Type | Volatility | Parallel |  Owner   | Security | Access privileges | Language |  Source code  | Description 
--------+-----------+--------------------------+------------------------------+------+------------+----------+----------+----------+-------------------+----------+---------------+-------------
tmp    | mysysdate | timestamp with time zone | OUT timestamp with time zone | func | stable     | unsafe   | postgres | invoker  |                   | sql      |              +| 
|           |                          |                              |      |            |          |          |          |                   |          | select now();+| 
|           |                          |                              |      |            |          |          |          |                   |          |               | 
(1 row)

---

注意：粒度不影响查询计划，

select date_trunc('sec', now());

还会导致索引扫描。

Answer 3

是的。这一定是波动性的事情。PG关于此事的文件。https://www.postgresql.org/docs/8.2/xfunc-volatility.html

他们显示"timeofday(("作为易失性的一个例子。

• now((-STABLE- 此查询开始的时间。在同一查询中调用它 6 次，它返回相同的时间。
• timeofday(( 和 sysdate(( -VOLATILE- 调用 time function(( 时的时间;而不是查询。这就像掏钱到操作系统的date工具。在同一查询中调用它 6 次，您将获得 6 次不同的时间。