為什麼未索引的範圍運算符 (<@) 比使用帶索引的 BETWEEN 更快？

注意 這與這個問題的設置相同，在這裡我專門詢問我在那邊沒有專門詢問的事情。

我有一個帶有 column 的表，列utc timestamptz上有一個“btree”索引utc：

CREATE TABLE foo(utc timestamptz)

CREATE INDEX ix_foo_utc ON foo (utc);

該表包含大約 5 億行數據。

當我過濾utcusingBETWEEN時，查詢計劃器按預期使用索引：

> EXPLAIN ANALYZE
SELECT
  utc
FROM foo
WHERE
   utc BETWEEN '2020-12-01' AND '2031-02-15'
;

Bitmap Heap Scan on foo  (cost=3048368.34..11836322.22 rows=143671392 width=8) (actual time=12447.905..165576.664 rows=150225530 loops=1)
 Recheck Cond: ((utc >= '2020-12-01 00:00:00+00'::timestamp with time zone) AND (utc <= '2031-02-15 00:00:00+00'::timestamp with time zone))
 Rows Removed by Index Recheck: 543231
 Heap Blocks: exact=43537 lossy=1818365
 ->  Bitmap Index Scan on ix_foo_utc  (cost=0.00..3012450.49 rows=143671392 width=0) (actual time=12436.236..12436.236 rows=150225530 loops=1)
    Index Cond: ((utc >= '2020-12-01 00:00:00+00'::timestamp with time zone) AND (utc <= '2031-02-15 00:00:00+00'::timestamp with time zone))
Planning time: 0.127 ms
Execution time: 172335.517 ms

我可以使用完全沒有索引的範圍運算符編寫相同的查詢：

> EXPLAIN ANALYZE
SELECT
  utc
FROM quotation.half_hour_data
WHERE
   utc <@ tstzrange('2020-12-01', '2031-02-15')
;

Gather  (cost=1000.00..9552135.30 rows=2556133 width=8) (actual time=0.179..145303.094 rows=150225530 loops=1)
 Workers Planned: 2
 Workers Launched: 2
 ->  Parallel Seq Scan on foo  (cost=0.00..9295522.00 rows=1065055 width=8) (actual time=5.321..117837.452 rows=50075177 loops=3)
     Filter: (utc <@ '["2020-12-01 00:00:00+00","2031-02-15 00:00:00+00")'::tstzrange)
     Rows Removed by Filter: 120333718
Planning time: 0.069 ms
Execution time: 153384.494 ms

它們正在執行相同的操作（儘管它<@是右手排他性和BETWEEN包容性的。）

未索引查詢如何<@比索引查詢更快BETWEEN？

當然，如果忽略索引更快，查詢計劃者應該提前知道嗎？

或者這是否與我的 PG 實例的記憶體量和查詢的大小有關（大！）

---

我的 Postgres 版本：

"PostgreSQL 10.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit"

首先，索引掃描比必要的要慢，因為您work_mem的大小不足以包含點陣圖。增加它，直到在點陣圖堆掃描期間不再獲得“有損”堆塊。

第二個計劃比第一個更快，因為它使用兩個並行工作程序（另一個計劃不能）。但是它使用了更多的資源：它使三個程序保持忙碌 117837 毫秒（加上額外的 27465 毫秒來收集結果），而點陣圖索引掃描在 153140 毫秒內完成。

引用自：https://dba.stackexchange.com/questions/285833