Subject: problem with btree indexes

From: John Peter Lee <[email-masked]>
Date: Thu, 2 Dec 1993 08:55:22 -0500 (EST)

Dear Postgres:

Hi, I sent a similar message earlier in the week, but never received
a follow-up to my follow-up question. Here goes again.

I'm running version 4.0.1 on a Sparcstation

I have a DB with ~55,000 records, 49 fields/record, all float4's.
It consumes ~14Mbytes. 

    When I query on a particular field, like so:

    retrieve (a.date)
    from a in comp_real_data
    where a.date < "0.20"\g

    I receive 4146 tuples back from Postgres. OK this is fine.

    I then built a btree index file on the "date" field, which
    consumed about 2Mbytes. There were 264 unique values for the
    field among the 55,000 records.

    create index date_index on comp_real_date using btree(date float4_ops)

    I then re-tried the original query, now with an index built on "date",
    and only received 1455 tuples back from Postgres!

Questions:

- there seems to be a problem with the btree code. Is this true, and what
  is this problem ??? Is this fixed in 4.1 ??? Has any btree code changed
  in version 4.1 ???

- Is the index file size of 2M reasonable for this field ? The btree itself
  looks small, but there are many records colliding for each unique "date"
  entry. 

thanks,

jp

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
JP Lee               Institute for Visualization and Perception Research	
[email-masked]                    University of Massachusetts at Lowell 
(508) 934-3384			      1 University Ave. Lowell, MA 01854

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

---

From: Marcel Kornacker <[email-masked]>
Date: Thu, 2 Dec 1993 14:07:18 -0800 (PST)

John:

Yes, there was a bug in the btree code that's fixed now. Version 
4.1 still contained that bug, the next release won't. The problem was in 
the insert routine, which made later searches in the btree overlook 
some values. The only workaround for you now is to remove the index; when 
you receive the next release you will have to rebuild this index and 
possible others which also have this "bug" in them. 

The 2M for the index is actually reasonable, since the btree has to store 
one key/tupleID-pair for each record in the indexed relation.

Marcel.