Re: How should we design our tables and indexes

On Tue, Feb 13, 2024 at 2:26 PM veem v <veema0000(at)gmail(dot)com> 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
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[]))

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