I create a table and insert some items.
I create index on every column.
And I execute select, I thought it should use index scan, but it is still seq scan. Why PG do not use index scan?
create table v_org_info(
org_no varchar2(8), org_nm varchar2(80),
org_no_l1 varchar2(8), org_nm_l1 varchar2(80),
org_no_l2 varchar2(8), org_nm_l2 varchar2(80)
);
create index idx_v_org_info_org_no on v_org_info(org_no);
create index idx_v_org_info_org_no_l1 on v_org_info(org_no_l1);
create index idx_v_org_info_org_no_l2 on v_org_info(org_no_l2);
begin
for i in 1..20000 loop
insert into v_org_info values(i,'test',i,'test',i,'test');
insert into adm_org_info values(i);
end loop;
end;
POSTGRES=# explain analyze select a.org_nm from v_org_info a where a.org_no = 1000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Seq Scan on V_ORG_INFO A (cost=0.00..189.97 rows=9 width=178, batch_size=100) (actual time=0.930..18.034 rows=1 loops=1)
Filter: (INT4IN(VARCHAROUT(ORG_NO)) = 1000)
Rows Removed by Filter: 19999
Total runtime: 18.099 ms
(4 rows)
