select on index column,why PG still use seq scan?

From: Xiaoyulei <xiaoyulei(at)huawei(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Cc: yetao <yetao1(at)huawei(dot)com>
Subject: select on index column,why PG still use seq scan?
Date: 2014-08-18 03:19:22
Message-ID: E8870A2F6A4B1045B1C292B77EAB207C5BA38112@SZXEMA501-MBX.china.huawei.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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)

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2014-08-18 03:49:42 Re: select on index column,why PG still use seq scan?
Previous Message Mark Kirkwood 2014-08-14 22:24:29 Re: 60 core performance with 9.3