Return-Path: owner-postman
Received: from localhost (localhost [127.0.0.1]) by nobozo.CS.Berkeley.EDU (8.6.4/8.6.3) with SMTP id FAA22405 for postgres-dist; Thu, 2 Dec 1993 05:55:27 -0800
Resent-From: POSTGRES mailing list <postman@postgres.Berkeley.EDU>
Resent-Message-Id: <199312021355.FAA22405@nobozo.CS.Berkeley.EDU>
X-Authentication-Warning: nobozo.CS.Berkeley.EDU: Host localhost didn't use HELO protocol
Sender: owner-postman@postgres.Berkeley.EDU
X-Return-Path: owner-postman
Received: from cs.uml.edu (cs.uml.edu [129.63.1.11]) by nobozo.CS.Berkeley.EDU (8.6.4/8.6.3) with SMTP id FAA22396 for <postgres@nobozo.CS.Berkeley.EDU>; Thu, 2 Dec 1993 05:55:26 -0800
Received: by cs.uml.edu id AA15916
  (5.67a+/IDA-1.5 for postgres@nobozo.berkeley.edu); Thu, 2 Dec 1993 08:55:23 -0500
From: John Peter Lee <jlee@cs.uml.edu>
Message-Id: <199312021355.AA15916@cs.uml.edu>
Subject: problem with btree indexes
To: postgres@postgres.Berkeley.EDU
Date: Thu, 2 Dec 1993 08:55:22 -0500 (EST)
Cc: jlee@cs.uml.edu (JP Lee), dpinkney@cs.uml.edu (Dave Pinkney),
        john@cs.uml.edu (John C. Sieg)
X-Mailer: ELM [version 2.4 PL22]
Content-Type: text
Content-Length: 1559      
Resent-To: postgres-dist@postgres.Berkeley.EDU
Resent-Date: Thu, 02 Dec 93 05:55:26 -0800
Resent-XMts: smtp

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	
jlee@cs.uml.edu                    University of Massachusetts at Lowell 
(508) 934-3384			      1 University Ave. Lowell, MA 01854

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