Regarding Plan tree output(Index/Bitmap Scan)
Hi,
I am trying to understand the Plan tree for select queries. Can you
please help me with the below queries?
1) Why is there a difference in plan tree for these two queries? User
table tidx1 has an index on column 'a' .
2) Why do we do Index scan and not Bitmap Index Scan for catalog tables?
postgres=# explain select * from pg_class where oid=2051;
QUERY PLAN
-------------------------------------------------------------------------------------
Index Scan using pg_class_oid_index on pg_class (cost=0.27..8.29
rows=1 width=265)
Index Cond: (oid = '2051'::oid)
(2 rows)
postgres=# explain select * from tidx1 where a=1;
QUERY PLAN
--------------------------------------------------------------------
Bitmap Heap Scan on tidx1 (cost=4.24..14.91 rows=11 width=8)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on idx1 (cost=0.00..4.24 rows=11 width=0)
Index Cond: (a = 1)
(4 rows)
postgres=# select * from tidx1;
a | b
---+---
1 | 2
2 | 2
3 | 2
4 | 2
5 | 2
(5 rows)
Best,
Aj
On Tue, Apr 11, 2023 at 06:09:41PM -0700, Ajay P S wrote:
I am trying to understand the Plan tree for select queries. Can you
please help me with the below queries?1) Why is there a difference in plan tree for these two queries? User
table tidx1 has an index on column 'a' .
Based on the query planner's cost estimate of the different scans.
2) Why do we do Index scan and not Bitmap Index Scan for catalog tables?
There's no reason why it can't happen in general.
But you queried pg_class on a unique column, returning at most one row.
A bitmap couldn't help by making the I/O more sequential. It can only
add overhead.
You can compare the costs of various plans by running EXPLAIN with
various enable_* GUCs to off.
BTW, your question should be directed to another list - this list is for
bug reports and development.
--
Justin