On Tue, Feb 13, 2024 at 2:26 PM veem v <veema0000@xxxxxxxxx> wrote:
Can the optimizer, only scan the TABLE1 using ACCESS criteria " TABLE1.MID in (XXXX)" or "TABLE1.CID in (XXXX)" which will be catered by two different index i.e one index on column "MID" and other on column "CID"?
Yes:
greg=# create table t1(pr_id int generated always as identity primary key, mid int, cid int);
CREATE TABLE
greg=# insert into t1(mid,cid) select random()*12345, random()*12345 from generate_series(1,123456);
INSERT 0 123456
greg=# create index t1_mid on t1(mid);
CREATE INDEX
greg=# create index t1_cid on t1(cid);
CREATE INDEX
CREATE TABLE
greg=# insert into t1(mid,cid) select random()*12345, random()*12345 from generate_series(1,123456);
INSERT 0 123456
greg=# create index t1_mid on t1(mid);
CREATE INDEX
greg=# create index t1_cid on t1(cid);
CREATE INDEX
greg=# analyze t1;
ANALYZE
greg=# explain select * from t1 where mid in (1,2,3,4) and cid IN (5,6,7,8);
QUERY PLAN
-------------------------------------------------------------------------------------------------
Bitmap Heap Scan on t1 (cost=50.03..109.55 rows=49 width=12)
Recheck Cond: ((cid = ANY ('{5,6,7,8}'::integer[])) AND (mid = ANY ('{1,2,3,4}'::integer[])))
-> BitmapAnd (cost=50.03..50.03 rows=49 width=0)
-> Bitmap Index Scan on t1_cid (cost=0.00..24.88 rows=2469 width=0)
Index Cond: (cid = ANY ('{5,6,7,8}'::integer[]))
-> Bitmap Index Scan on t1_mid (cost=0.00..24.88 rows=2469 width=0)
Index Cond: (mid = ANY ('{1,2,3,4}'::integer[]))
greg=# explain select * from t1 where mid in (1,2,3,4) and cid IN (5,6,7,8);
QUERY PLAN
-------------------------------------------------------------------------------------------------
Bitmap Heap Scan on t1 (cost=50.03..109.55 rows=49 width=12)
Recheck Cond: ((cid = ANY ('{5,6,7,8}'::integer[])) AND (mid = ANY ('{1,2,3,4}'::integer[])))
-> BitmapAnd (cost=50.03..50.03 rows=49 width=0)
-> Bitmap Index Scan on t1_cid (cost=0.00..24.88 rows=2469 width=0)
Index Cond: (cid = ANY ('{5,6,7,8}'::integer[]))
-> Bitmap Index Scan on t1_mid (cost=0.00..24.88 rows=2469 width=0)
Index Cond: (mid = ANY ('{1,2,3,4}'::integer[]))
It can utilize other columns as access criteria those used in join conditions like MID, PR_ID, in which case a composite index on the columns(CID,PR_ID) (MID, PR_ID) will provide better selectivity and faster access?
If you query on the primary key, it's going to use the associated PK index, not a composite one in which the PK is buried. But try creating the sample table t1 above yourself and play around with the various indexes and query combinations.
Cheers,
Greg
