PostgreSQL – Understanding Bitmap Heap Scan and Seq Scan

execution-planindexperformancepostgresqlquery-performance

I'm trying to understand that which scan is efficient, Here is the explain for the query,

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))  

above view created as below,

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;

Planner output is,
Without 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))"

and after adding index like below,

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

Query planner out is,

"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)"

So, here which one is suggestible to execute the query with index or without?

Appreciated for the responses.

Thank you

Best Answer

The planner thinks the bitmap scan is faster. If you want to know which is actually faster, run them and see. To get the best info, turn on track_io_timing and run the query with EXPLAIN (ANALYZE, BUFFERS). If your system has slow clock access, you might want to add TIMING OFF.

There is no magic source of truth we can reveal to you just by looking at your post. If there were, we would make the planner use it; and then it would never be wrong.