主页 / dba / 问题 / 264711
Accepted
Rj_N
Asked: 2020-04-10 11:27:57 +0800 CST

理解“位图堆扫描”和“序列扫描”

  • 772

我试图了解哪种扫描有效，这是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)"

那么，这里哪一个是建议使用索引或不使用索引来执行查询的呢？

感谢您的回复。

谢谢

postgresql performance

1 个回答

  1. Best Answer

    规划者认为位图扫描更快。如果您想知道哪个实际上更快，请运行它们并查看。要获得最佳信息，请打开track_io_timing并运行查询EXPLAIN (ANALYZE, BUFFERS)。如果您的系统时钟访问速度较慢，您可能需要添加 TIMING OFF。

    仅通过查看您的帖子，我们就无法向您揭示真相的神奇来源。如果有，我们会让规划者使用它；然后它就永远不会错了。

    • 1

相关问题