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)
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 |