使用内部连接时未使用Postgres索引

我的数据库中有下表:

business_db_dev=# \d schedules2
                                          Table "public.schedules2"
   Column    |              Type              | Collation | Nullable |                Default                 
-------------+--------------------------------+-----------+----------+----------------------------------------
 id          | bigint                         |           | not null | nextval('schedules2_id_seq'::regclass)
 monday      | boolean                        |           | not null | 
 tuesday     | boolean                        |           | not null | 
 wednesday   | boolean                        |           | not null | 
 thursday    | boolean                        |           | not null | 
 friday      | boolean                        |           | not null | 
 saturday    | boolean                        |           | not null | 
 sunday      | boolean                        |           | not null | 
 start1      | time(0) without time zone      |           |          | 
 end1        | time(0) without time zone      |           |          | 
 start2      | time(0) without time zone      |           |          | 
 end2        | time(0) without time zone      |           |          | 
 user_id     | bigint                         |           | not null | 
 inserted_at | timestamp(0) without time zone |           | not null | 
 updated_at  | timestamp(0) without time zone |           | not null | 
Indexes:
    "schedules2_pkey" PRIMARY KEY, btree (id)
    "schedules2_start1_end1_DESC_NULLS_LAST_index" btree (start1, end1 DESC NULLS LAST)
    "schedules2_start2_end2_DESC_NULLS_LAST_index" btree (start2, end2 DESC NULLS LAST)
    "schedules2_user_id_index" UNIQUE, btree (user_id)
Foreign-key constraints:
    "schedules2_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

users 和 strategies

给出这个表,我尝试执行以下查询

select u.token
from strategies as st
inner join users as u on (st.user_id = u.id)
inner join schedules2 as sc on (st.user_id = sc.user_id)
where st.exchange = 'binance'
      and st.market_pair = 'btc_usdt'
      and st.timeframe = 'five_minutes'
      and st.name = 'stoch_oscillator'
      and st.inputs = '{5,3,3,80,20}'
      and (sc.start1 is null or ('13:00:01'::time between sc.start1 and sc.end1) or ('13:00:01'::time between sc.start2 and sc.end2));

使用运行此查询 explain analyze 我得到了这个结果:

 Nested Loop  (cost=1.27..215.56 rows=16 width=6) (actual time=0.076..6.050 rows=942 loops=1)
   Join Filter: (st.user_id = u.id)
   ->  Nested Loop  (cost=0.98..197.89 rows=17 width=16) (actual time=0.070..3.650 rows=942 loops=1)
         ->  Index Only Scan using unique_strategy_and_user_id on strategies st  (cost=0.69..7.29 rows=80 width=8) (actual time=0.056..1.083 rows=1000 loops=1)
               Index Cond: ((exchange = 'binance'::text) AND (market_pair = 'btc_usdt'::text) AND (timeframe = 'five_minutes'::text) AND (name = 'stoch_oscillator'::text) AND (inputs = '{5,3,3,80,20}'::character varying[]))
               Heap Fetches: 0
         ->  Index Scan using schedules2_user_id_index on schedules2 sc  (cost=0.29..2.38 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1000)
               Index Cond: (user_id = st.user_id)
               Filter: ((start1 IS NULL) OR (('13:00:01'::time without time zone >= start1) AND ('13:00:01'::time without time zone <= end1)) OR (('13:00:01'::time without time zone >= start2) AND ('13:00:01'::time without time zone <= end2)))
               Rows Removed by Filter: 0
   ->  Index Scan using users_pkey on users u  (cost=0.29..1.03 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=942)
         Index Cond: (id = sc.user_id)
 Planning Time: 0.834 ms
 Execution Time: 6.130 ms

重要的部分是:

Index Scan using schedules2_user_id_index on schedules2 sc  (cost=0.29..2.38 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1000)
   Index Cond: (user_id = st.user_id)
   Filter: ((start1 IS NULL) OR (('13:00:01'::time without time zone >= start1) AND ('13:00:01'::time without time zone <= end1)) OR (('13:00:01'::time without time zone >= start2) AND ('13:00:01'::time without time zone <= end2)))

正如你所见,Postgres正在使用 Filter 检查值 start1 , end1 , start2 和 end2 ,但我希望Postgres会使用我为这种情况创建的两个索引:

"schedules2_start1_end1_DESC_NULLS_LAST_index" btree (start1, end1 DESC NULLS LAST)
"schedules2_start2_end2_DESC_NULLS_LAST_index" btree (start2, end2 DESC NULLS LAST)

使用删除连接 schedules2 表及其条件基本上使查询时间减半。

所以,我的问题是,为什么Postgres使用 滤器 如何更改查询或索引本身以优化此查询?

编辑:请注意,查询中使用的值(如 '13:00:01'::time