From: Tom Lane <tgl@sss.pgh.pa.us>
To: Xiaoyulei <xiaoyulei@huawei.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: yetao <yetao1@huawei.com>
Subject: Re: select on index column,why PG still use seq scan?
Date: Sun, 17 Aug 2014 23:49:42 -0400
Message-ID: <24243.1408333782@sss.pgh.pa.us> (raw)
In-Reply-To: <E8870A2F6A4B1045B1C292B77EAB207C5BA38112@SZXEMA501-MBX.china.huawei.com>
References: <E8870A2F6A4B1045B1C292B77EAB207C5BA38112@SZXEMA501-MBX.china.huawei.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, xiaoyulei@huawei.com, yetao1@huawei.com
Subject: Re: select on index column,why PG still use seq scan?
In-Reply-To: <24243.1408333782@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox