理解“點陣圖堆掃描”和“序列掃描”

我試圖了解哪種掃描有效，這是explain查詢的內容，

EXPLAIN SELECT *
FROM 'myTable_v' --view
WHERE cast(TIMESTAMP AS VARCHAR(100)) > CAST(1586415955169 AS VARCHAR(100))
AND cast(TIMESTAMP AS VARCHAR(100)) <= CAST(1586416090443 AS VARCHAR(100))  

上面的視圖創建如下，

CREATE VIEW 'myTable_v' AS
SELECT 
msg ->> 'customerType'::text AS customertype,
msg ->> 'trackingId'::text AS "trackingId",
msg ->> 'conversationId'::text AS "conversationId",
msg ->> 'authState'::text AS authstate,
msg ->> 'tmoId'::text AS id,
msg ->> 'msisdn'::text AS sdn,
msg ->> 'ban'::text AS ban,
FROM myTable -- main table
WHERE (msg ->> 'type'::text) = 'CTYPE'::text;

規劃器輸出是，沒有index

"Seq Scan on 'myTable' (cost=0.00..472877.98 rows=7300 width=904)"
"  Filter: (((msg ->> 'type'::text) = 'EdsConversationData'::text) AND ((("timestamp")::character varying(100))::text > '1586415955169'::text) AND ((("timestamp")::character varying(100))::text <= '1586416090443'::text))"

在添加如下索引後，

CREATE INDEX idx_timestmp_n ON 'myTable' ( (msg ->> 'type'),((('timestamp')::character varying(100))))

查詢規劃器出來的是，

"Bitmap Heap Scan on 'myTable'  (cost=505.45..49649.59 rows=7300 width=904)"
"  Recheck Cond: ((msg ->> 'type'::text) = 'EdsConversationData'::text)"
"  Filter: (((("timestamp")::character varying(100))::text > '1586415955169'::text) AND ((("timestamp")::character varying(100))::text <= '1586416090443'::text))"
"  ->  Bitmap Index Scan on idx_timestmp_n  (cost=0.00..505.43 rows=14517 width=0)"
"        Index Cond: ((msg ->> 'type'::text) = 'EdsConversationData'::text)"

那麼，這裡哪一個是建議使用索引或不使用索引來執行查詢的呢？

感謝您的回复。

謝謝

規劃者認為點陣圖掃描更快。如果您想知道哪個實際上更快，請執行它們並查看。要獲得最佳資訊，請打開track_io_timing並執行查詢EXPLAIN (ANALYZE, BUFFERS)。如果您的系統時鐘訪問速度較慢，您可能需要添加 TIMING OFF。

僅通過查看您的文章，我們就無法向您揭示真相的神奇來源。如果有，我們會讓規劃者使用它；然後它就永遠不會錯了。

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