Thread: select on index column,why PG still use seq scan?
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)
Xiaoyulei <xiaoyulei@huawei.com> writes:
> 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)
> );
There is no "varchar2" type in Postgres. I tried this example with
"varchar" in place of that, but when I got to
> POSTGRES=# explain analyze select a.org_nm from v_org_info a where a.org_no = 1000;
I got
ERROR: operator does not exist: character varying = integer
LINE 1: ...ze select a.org_nm from v_org_info a where a.org_no = 1000;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
which is certainly what I *should* get. I changed it to
explain analyze select a.org_nm from v_org_info a where a.org_no = '1000';
and then I got
Bitmap Heap Scan on v_org_info a (cost=4.49..74.90 rows=27 width=58) (actual t
ime=0.044..0.044 rows=1 loops=1)
Recheck Cond: ((org_no)::text = '1000'::text)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_v_org_info_org_no (cost=0.00..4.48 rows=27 widt
h=0) (actual time=0.020..0.020 rows=1 loops=1)
Index Cond: ((org_no)::text = '1000'::text)
Planning time: 0.481 ms
Execution time: 0.104 ms
which is OK, but after "ANALYZE v_org_info" I got
Index Scan using idx_v_org_info_org_no on v_org_info a (cost=0.29..8.30 rows=1
width=5) (actual time=0.019..0.020 rows=1 loops=1)
Index Cond: ((org_no)::text = '1000'::text)
Planning time: 0.372 ms
Execution time: 0.060 ms
which is better.
> 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)
TBH, this looks like some incompetently hacked-up variant of Postgres;
certainly no version ever shipped by the core project would have done
this. It looks like somebody tried to make cross-type comparisons work by
inserting conversion operations, but they did it in such a way that the
conversions were applied to the column not the constant. An index on
org_no isn't going to help you for a query on INT4IN(VARCHAROUT(ORG_NO)).
(And I wonder why exactly the names are printing as upper case here ...)
regards, tom lane