Thread: btree index on a char(8) field (fwd)
Hi, I sent out the forwarded message below last Sunday, and have received no responses. Now I realize that this list operates on a volunteer basis and that everyone is busy, so I am in no way demanding or expecting an answer, but I would really like to know if there is something wrong with the message that caused the lack of response. Does it contain too much or too little detail? Is it an obvious RTFM question? Is this is the correct mailing list? Basically, I am trying to make use of a btree index on a char(8) field, the optimizer doesn't want to use it, and I would like to know why. I was hoping that there might be a simple explanation. I have searched the PostgreSQL documentation and the mailing list archives without success. I would *greatly* appreciate any response to this message or the forwarded message, no matter how brief. Thanks in advance, ../fam ---------- Forwarded message ---------- Date: Sun, 3 Oct 1999 19:50:37 -0400 From: Frank Mandarino <fam@dbsys.risca.com> To: pgsql-general@postgreSQL.org Subject: [GENERAL] btree index on a char(8) field I am in the process of migrating a database from Postgres95 2.0 to PostgreSQL 6.5.2 on a Debian 2.1 system. In a few of the tables, a char8 type field was used for the primary key, so I converted them to type char(8). The tables also had a btree index built on the primary key using char8_ops, which I converted to char_ops. Now I am finding that explain is indicating that the index is never used, even for queries that I would have thought would run faster using an index. For example: main=> \d vendor Table = vendor +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | ven_code | char() | 8 | | initials | char() | 2 | | tax_number | text | var | | check_payable_to | text | var | | other_name | text | var | | address_1 | text | var | | address_2 | text | var | | city | text | var | | ps | text | var | | country | text | var | | postal_code | text | var | | work_phone | text | var | | home_phone | text | var | | fax_phone | text | var | | bank_code | text | var | | trans_num | text | var | | bank_acc_num | text | var | | payment_restriction_flag | char() | 1 | | debt_reason_msg | text | var | | debt_caution_msg | text | var | | comments_1 | text | var | | comments_2 | text | var | | special_order | char() | 1 | | status | char() | 1 | +----------------------------------+----------------------------------+-------+ main=> select count(*) from vendor; count ----- 9905 (1 row) main=> create index ven_code_idx on vendor using btree (ven_code char_ops); CREATE main=> vacuum analyze; VACUUM main=> explain select ven_code,initials,city from vendor where ven_code='P8979'; NOTICE: QUERY PLAN: Seq Scan on vendor (cost=738.86 rows=2 width=36) EXPLAIN main=> Under Postgres95, the index was used from such queries. Can anyone tell me why the index isn't being used in PostgreSQL? Thanks, ../fam -- Frank A. Mandarino fam@risca.com ************
At 15:30 +0200 on 06/10/1999, Frank Mandarino wrote: > main=> create index ven_code_idx on vendor using btree (ven_code char_ops); > CREATE I didn't have time to check this, but the problem may be caused by the incorrect ops you are using. For char(N) it should be bpchar_ops, not char_ops. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
Hermouth,
Thank you so much for your reply! You were exactly right. I re-created
the index with bpchar_ops, and explain now outputs:
Index Scan using ven_code_idx on vendor (cost=2.05 rows=2 width=36)
instead of
Seq Scan on vendor (cost=738.86 rows=2 width=36)
Queries are obviously much faster now.
I am unable to find any reference to bpchar_ops in the the documentation
or the General and SQL mailing list archives. Can you tell me where I
could find out more about what "_ops" are available and what they all
mean?
Thanks again,
../fam
--
Frank A. Mandarino
fam@risca.com
On Wed, 6 Oct 1999, Herouth Maoz wrote:
> At 15:30 +0200 on 06/10/1999, Frank Mandarino wrote:
>
>
> > main=> create index ven_code_idx on vendor using btree (ven_code char_ops);
> > CREATE
>
> I didn't have time to check this, but the problem may be caused by the
> incorrect ops you are using. For char(N) it should be bpchar_ops, not
> char_ops.
>
> Herouth
>
> --
> Herouth Maoz, Internet developer.
> Open University of Israel - Telem project
> http://telem.openu.ac.il/~herutma
>
>
>
> ************
>
> I am unable to find any reference to bpchar_ops in the the documentation
> or the General and SQL mailing list archives. Can you tell me where I
> could find out more about what "_ops" are available and what they all
> mean?
The direct answer:
$ pwd
/usr/src/postgresql-6.5/doc/src/sgml
$ find -name "*sgml" -exec grep -il "_ops" {} \;
./ref/create_index.sgml
./arch-dev.sgml -- irrelevant: co-incidental with a processing directive, \label{simple_set_ops})
./bki.sgml
./gist.sgml
./xindex.sgml
My comment:
The deficiency of the docs in regards to operator classes probably
results from the fact that no one is asking about those. The opclass
parameter in CREATE INDEX is no longer required (Herouth has been
around long enough to recall the times when it was).
As you have just witnessed, in a standard situation, you are better
off without knowing about it -- postgres will pick the right opclass
for you. That will not happen, however, when the values you want to
index are of a custom type, or when a built-in type does not have an
opclass of its own (as is the case with the point type). Also, you
need this option to override the default opclass for those types that
can work with multiple opclasses (which is what you attempted to
achieve).
Will anyone with a solid knowledge of the type system want to augment
the existing docs?
--Gene
Gene, Thanks for your response. I knew from the programming documentation that the opclass was optional. I'm pretty sure, although I will check again tonight, that I tried creating the index without specifying the opclass, but I found that the index was still not used in my example query. Do you know which opclass that Postgres should choose for char(8) types? Regards, ../fam -- Frank A. Mandarino fam@risca.com On Thu, 7 Oct 1999, Gene Selkov, Jr. wrote: > > My comment: > > The deficiency of the docs in regards to operator classes probably > results from the fact that no one is asking about those. The opclass > parameter in CREATE INDEX is no longer required (Herouth has been > around long enough to recall the times when it was). > > As you have just witnessed, in a standard situation, you are better > off without knowing about it -- postgres will pick the right opclass > for you. That will not happen, however, when the values you want to > index are of a custom type, or when a built-in type does not have an > opclass of its own (as is the case with the point type). Also, you > need this option to override the default opclass for those types that > can work with multiple opclasses (which is what you attempted to > achieve). > > Will anyone with a solid knowledge of the type system want to augment > the existing docs? > > --Gene > > ************ >
On Thu, 7 Oct 1999, Frank Mandarino wrote: > Gene, > > Thanks for your response. > > I knew from the programming documentation that the opclass was optional. > I'm pretty sure, although I will check again tonight, that I tried > creating the index without specifying the opclass, but I found that the > index was still not used in my example query. > > Do you know which opclass that Postgres should choose for char(8) types? > > Regards, > ../fam > -- Frank A. Mandarino fam@risca.com Infrasoft Inc. (905) 877-1752 Days: (416) 350-5261
Frank Mandarino wrote: > Thanks for your response. > > I knew from the programming documentation that the opclass was optional. > I'm pretty sure, although I will check again tonight, that I tried > creating the index without specifying the opclass, but I found that the > index was still not used in my example query. > > Do you know which opclass that Postgres should choose for char(8) types? Owing very much to Franks question, I looked around and made the following discovery. I have always been puzzled why only my own types require an opclass in CREATE INDEX. The answer is that I failed to provide the default. I based my code on the outdated postgres schema, which still exists in the docs: http://www.postgresql.org/docs/programmer/extend289.htm (I'd love to see it fixed one day!) If I got it right, the default opclass is snow specified in pg_opclass: SELECT DISTINCT pg_am.amname, pg_opclass.opcname, pg_type.typname FROM pg_am, pg_amop, pg_opclass, pg_type WHERE pg_amop.amopid = pg_am.oid AND pg_amop.amopclaid = pg_opclass.oid AND pg_opclass.opcdeftype = pg_type.oid; amname|opcname |typname ------+------------+-------- btree |abstime_ops |abstime btree |bpchar_ops |bpchar btree |char_ops |char btree |date_ops |date btree |datetime_ops|datetime btree |float4_ops |float4 btree |float8_ops |float8 btree |int2_ops |int2 btree |int4_ops |int4 btree |int8_ops |int8 btree |macaddr_ops |macaddr btree |name_ops |name btree |network_ops |cidr btree |network_ops |inet btree |oid8_ops |oid8 btree |oid_ops |oid btree |text_ops |text btree |time_ops |time btree |timespan_ops|timespan btree |varchar_ops |varchar hash |bpchar_ops |bpchar hash |char_ops |char hash |date_ops |date hash |datetime_ops|datetime hash |float4_ops |float4 hash |float8_ops |float8 hash |int2_ops |int2 hash |int4_ops |int4 hash |int8_ops |int8 hash |macaddr_ops |macaddr hash |name_ops |name hash |network_ops |cidr hash |network_ops |inet hash |oid8_ops |oid8 hash |oid_ops |oid hash |text_ops |text hash |time_ops |time hash |timespan_ops|timespan hash |varchar_ops |varchar rtree |bigbox_ops |box rtree |box_ops |box rtree |circle_ops |circle rtree |poly_ops |polygon (43 rows) The way I understand it is that for each access method (amname) and data type (typname) the default opclass is specified in (opcname). This doesn't tell you, however, that char(n) is a bpchar (thanks to SQL92 compliance, you can't anymore find that out by just looking at it). Also, the above query does not list the types not having a default opclass: SELECT * FROM pg_opclass WHERE opcdeftype IS NULL; opcname |opcdeftype ------------+---------- ec_code_ops | gist_seg_ops| And by the way, what I have just found makes me believe that one does not even have to mention the access method ("using"-clause) in their CREATE INDEX. "If nothing else helps, read the manual" --Gene