POSTGRESQL 短查询优化，独立索引与组合索引 8

标签：scheduled POSTGRESQL 08 departure 查询索引 2020

POSTGRESQL 短查询优化，独立索引与组合索引 8_索引

这是一个关于POSTGRESQL 查询的优化系列，这已经是这个系列的第八集了，接上期，在OLTP查询中我们需要注意的查询优化的地方非常多，稍不留意就会在一些问题上的操作导致查询的数据逻辑错误。继续上次的问题，在查询中，针对事件的查询问题，我们一般处理的模式

1 针对具体事件字段的时间标注清晰，不要用年月日的方式来表达，而是要使用具体的秒的方式。

2 BETWEEN 的方式进行查询时要明确BETWEEN 的含义是>= <= 而不是 > < 的含义。

那么我们看看我们建立了索引后，具体的查询情况

很明显第一种查询中，查询的计划走的是全表扫描并行的方式，主要的原因在上一期已经提到过了。

POSTGRESQL 短查询优化，独立索引与组合索引 8_数据库_02

所以我们必须在建立索引的情况下，要不对索引的建立方式通过函数索引的方式建立索引，或者在查询的条件上做文章。

我们首先看下面两个查询的方式的执行计划，下面两个查询唯一不同的是在条件中没有使用和使用了字符的转义，如果不明确走的是带有时区的方式，如果标识了则带有的是不带有时区的方式。下图我们可以很清晰的看到两个执行计划中的细微的变化。

POSTGRESQL 短查询优化，独立索引与组合索引 8_数据库_03

POSTGRESQL 短查询优化，独立索引与组合索引 8_mysql_04

set search_path to postgres_air;
explain SELECT * FROM flight
WHERE scheduled_departure ::date
BETWEEN '2020-08-17' AND '2020-08-18';

explain (verbose) SELECT * FROM flight
WHERE scheduled_departure
BETWEEN '2020-08-17 ' AND '2020-08-18';

create index idx_flight_scheduled on flight (scheduled_departure);

explain (verbose) SELECT * FROM flight
WHERE scheduled_departure
BETWEEN '2020-08-17'::timestamp AND '2020-08-18'::timestamp;

部分情况下，针对这样的情况向通过创建函数索引并不能成功，例如我们想创建一个函数，通过自定义函数进行相关函数索引的建立。通过系统函数或自定义函数创建函数索引，必须保证创建的索引是稳定的，如果破坏了规则，则无论用什么办法都无法建立函数索引，错误见下图

POSTGRESQL 短查询优化，独立索引与组合索引 8_python_05

POSTGRESQL 短查询优化，独立索引与组合索引 8_索引_06

我们在通过一个例子来说明部分情况下，需要针对某些逻辑来重写SQL 的必要性，很明显下面的查询并没有走索引，因为就是条件的左侧使用了索引。

SELECT * FROM flight
WHERE coalesce(actual_departure, scheduled_departure)
BETWEEN '2020-08-17' AND '2020-08-18'；

这个查询的含义就是判断第一个字段是否为空值，如果为，则显示的数据为第二个字段的值。

POSTGRESQL 短查询优化，独立索引与组合索引 8_索引_07

我们换一个写法，这样能使用到索引

explain SELECT * FROM flight
WHERE (actual_departure
BETWEEN '2020-08-17' AND '2020-08-18')
OR (actual_departure IS NULL
AND scheduled_departure BETWEEN '2020-08-17' AND '2020-08-18');

POSTGRESQL 短查询优化，独立索引与组合索引 8_mysql_08

对比上面两种写法，最终的执行时间，不改写的时间为 71ms

POSTGRESQL 短查询优化，独立索引与组合索引 8_索引_09

而换了写法后，我们获得的执行时间为1.148ms

POSTGRESQL 短查询优化，独立索引与组合索引 8_python_10

在讲完上述的内容后，我们下一步是针对索引中有多个字段的问题来说说，一般来说，有两种情况

1 多个字段，分别建立索引，在查询中，使用多个索引查询数据

2 多个字段建立一个索引，在查询中使用这个索引作为查询的索引使用

那么到底哪种好，我们应该再怎么做，根据原理的分析，如果我们建立多个索引，那么POSTGRESQL 也是可以利用这些索引的，通过在查询中使用这些索引，并找到对应数据块，在系统中建立位图信息，最终根据条件将这些块通过AND 或者 OR 的方式将数据组合。

set search_path to postgres_air;
explain SELECT scheduled_departure ,
scheduled_arrival
FROM flight
WHERE departure_airport='ORD' AND arrival_airport='JFK'
AND scheduled_departure BETWEEN '2020-07-03' AND '2020-07-04';

POSTGRESQL 短查询优化，独立索引与组合索引 8_索引_11