postgresql不要在timestamptz列上使用索引

Question

表上的索引：

create index shifts_start_at_idx
    on shifts (start_at);

与时区域的查询1：

SELECT shifts.id

FROM shifts
         JOIN stores ON shifts.store_id = stores.id AND stores.deleted_at IS NULL
         JOIN cities ON stores.city_id = cities.id
WHERE TRUE
  AND (shifts.start_at >= '2022-05-06 03:00:00'::timestamp AT TIME ZONE
                          (EXTRACT(timezone FROM cities.time_zone) * INTERVAL '1 second'))
ORDER BY shifts.start_at DESC, shifts.end_at DESC, shifts.id DESC
LIMIT 100;

解释查询1：

Limit  (cost=0.86..298.93 rows=100 width=24) (actual time=0.143..25.257 rows=100 loops=1)
  ->  Nested Loop  (cost=0.86..1485256.59 rows=498300 width=24) (actual time=0.131..23.317 rows=100 loops=1)
"        Join Filter: (shifts.start_at >= timezone((date_part('timezone'::text, cities.time_zone) * '00:00:01'::interval), '2022-05-06 03:00:00'::timestamp without time zone))"
        ->  Nested Loop  (cost=0.72..1209695.67 rows=1494900 width=32) (actual time=0.096..17.621 rows=100 loops=1)
              ->  Index Scan Backward using shifts_admin_order_by_idx on shifts  (cost=0.43..291132.79 rows=3000000 width=32) (actual time=0.036..6.780 rows=205 loops=1)
              ->  Index Scan using stores_id_deleted_at_null_idx on stores  (cost=0.29..0.31 rows=1 width=16) (actual time=0.025..0.025 rows=0 loops=205)
                    Index Cond: (id = shifts.store_id)
        ->  Index Scan using cities_pkey on cities  (cost=0.14..0.16 rows=1 width=20) (actual time=0.017..0.017 rows=1 loops=100)
              Index Cond: (id = stores.city_id)
Planning Time: 0.632 ms
Execution Time: 26.436 ms

Postgres不使用索引

查询2没有时区域：

SELECT shifts.id

FROM shifts
         JOIN stores ON shifts.store_id = stores.id AND stores.deleted_at IS NULL
         JOIN cities ON stores.city_id = cities.id
WHERE TRUE
  AND (shifts.start_at >= '2022-05-06 03:00:00')
ORDER BY shifts.start_at DESC, shifts.end_at DESC, shifts.id DESC
LIMIT 100;

解释查询2：

Limit  (cost=0.86..108.84 rows=100 width=24) (actual time=0.125..8.866 rows=100 loops=1)
  ->  Nested Loop  (cost=0.86..898691.17 rows=832261 width=24) (actual time=0.115..7.886 rows=100 loops=1)
        ->  Nested Loop  (cost=0.72..761958.37 rows=832261 width=32) (actual time=0.066..5.570 rows=100 loops=1)
              ->  Index Scan Backward using shifts_admin_order_by_idx on shifts  (cost=0.43..248984.02 rows=1670200 width=32) (actual time=0.014..1.380 rows=205 loops=1)
                    Index Cond: (start_at >= '2022-05-06 03:00:00+00'::timestamp with time zone)
              ->  Index Scan using stores_id_deleted_at_null_idx on stores  (cost=0.29..0.31 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=205)
                    Index Cond: (id = shifts.store_id)
        ->  Index Only Scan using cities_pkey on cities  (cost=0.14..0.16 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=100)
              Index Cond: (id = stores.city_id)
              Heap Fetches: 100
Planning Time: 0.327 ms
Execution Time: 9.394 ms

尚不完全清楚为什么它在转换时间时不想使用索引达到时区的时间格式