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

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

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

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

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

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

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

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

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;

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

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

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

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

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

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');

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

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

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

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';

针对上方的多个索引通过BITMAP的方式查询，我们更多希望的是通过一个组合索引来进行查询。

我们在创建相关的复合索引后再进行查询

需要注意的是组合索引的问题，在建立索引时假设和上面一样包含三个字段

A B C, 索引可以命中的查询不需要完全和索引重合，但需要满足以下条件

查询字段方式以及顺序

1  A B C

2  A C 

3  A B

都是可以命中索引的。但下面的情况就不会命中索引

总结

1  B C 

2  B

3  C

等方式都不会命中组合索引，所以组合索引要命中的第一点就是，查询中必须带有索引中第一个字段，否则组合索引就失效了。

基于上面的单独索引和组合索引我们看一下之间的优缺点。

1  多个索引的方式适合查询中的写法更灵活的情况下，尤其对于组合索引的第一个字段无法命中的情况。

2 独立的多个索引和组合索引比较，在查询的方式比较独立的情况下，组合索引查询的效率要比单独索引效率高。

前期

Postgresql   SQL 优化   --full scan  index scan  index only 的区别

https://mp.weixin.qq.com/s?__biz=Mzg4NDA0NTEwNA==&mid=2247494612&idx=1&sn=e5222627411adfc51a251abffcab423f&chksm=cfbc8f8bf8cb069da9fb78e48d3313aeee9a20545173c8153cdfc91f1e41ddf82be7128347cd&token=695620555&lang=zh_CN#rd

POSTGRESQL SQL优化 重优化轻设计对不对与优化需要掌握的知识类别

https://mp.weixin.qq.com/s?__biz=Mzg4NDA0NTEwNA==&mid=2247494440&idx=1&sn=7eaf6a22b78f8229376fa8c4a3f48bc6&chksm=cfbc8f77f8cb0661a2db86558b347ee654a31284934cccd69cb3451968c3b4c47563d61802a7&token=160431904&lang=zh_CN#rd

postgresql SQL 优化 -- 理论与原理

https://mp.weixin.qq.com/s?__biz=Mzg4NDA0NTEwNA==&mid=2247494506&idx=1&sn=61dfd3d8a7ccaba32321bb2f5a61d665&chksm=cfbc8f35f8cb0623728dcef8dbb6c1dd46ad884e7f370dfd04e66117de779dce15c80b76a541&token=2088516272&lang=zh_CN#rd

Postgresql  SQL 优化  两个模型与数据存储