為什麼 NULL 排序會影響 PostgreSQL 中的 btree 掃描？

在這個答案中，jjanes 說與索引聲明排序相反的 NULL ORDERING 可以強制進行快速掃描。事實證明他是對的。我想知道為什麼會這樣。如果您是最後一個或第一個帶有空值的 btree，為什麼不能跳過它們？

CREATE TABLE foo AS
 SELECT x::int
 FROM generate_series(1,1e6) AS gs(x);

CREATE INDEX ON foo(x DESC NULLS FIRST);

ANALYZE foo;

與NULLS LAST,

SELECT * FROM foo WHERE x BETWEEN 100 AND 5000 ORDER BY x DESC NULLS LAST;

                                                           QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
Sort  (cost=414.95..426.19 rows=4494 width=4) (actual time=2.660..3.147 rows=4901 loops=1)
  Sort Key: x DESC NULLS LAST
  Sort Method: quicksort  Memory: 422kB
  ->  Index Only Scan using foo_x_idx on foo  (cost=0.42..142.31 rows=4494 width=4) (actual time=0.031..1.522 rows=4901 loops=1)
        Index Cond: ((x >= 100) AND (x <= 5000))
        Heap Fetches: 0
Planning time: 0.265 ms
Execution time: 3.633 ms
(8 rows)

沒有NULLS LAST，

SELECT * FROM foo WHERE x BETWEEN 100 AND 5000 ORDER BY x DESC;
                                                        QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
Index Only Scan using foo_x_idx on foo  (cost=0.42..142.31 rows=4494 width=4) (actual time=0.040..1.611 rows=4901 loops=1)
  Index Cond: ((x >= 100) AND (x <= 5000))
  Heap Fetches: 0
Planning time: 0.192 ms
Execution time: 2.021 ms
(5 rows)

與索引聲明相反的 NULL 排序似乎強制快速掃描而不是僅索引。雖然我不知道為什麼。

如果列是，它甚至會發生NOT NULL，

ALTER TABLE foo ALTER COLUMN x SET NOT NULL;
VACUUM ANALYZE foo;
SELECT * FROM foo WHERE x BETWEEN 100 AND 5000 ORDER BY x DESC NULLS LAST;
                                                           QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
Sort  (cost=477.64..490.43 rows=5113 width=4) (actual time=2.681..3.173 rows=4901 loops=1)
  Sort Key: x DESC NULLS LAST
  Sort Method: quicksort  Memory: 422kB
  ->  Index Only Scan using foo_x_idx on foo  (cost=0.42..162.69 rows=5113 width=4) (actual time=0.029..1.506 rows=4901 loops=1)
        Index Cond: ((x >= 100) AND (x <= 5000))
        Heap Fetches: 0
Planning time: 0.225 ms
Execution time: 3.662 ms
(8 rows)

我在irc中問了這個問題，似乎他們從來沒有這樣做過。

< johto> EvanCarroll; "because nobody implemented it" is usually the answer
< macdice> EvanCarroll: we absolutely could implement that and it's been discussed in this channel.  the codez have not been forthcoming
< macdice> an index scan that is smart enough to skip null (start after null) and then skip back to the nulls at the end
< macdice> probably the easiest of many potential skip scans tricks we could teach indexes to do

（等待其他可能的答案，我會接受這個）

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