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 SAA28896 for postgres-dist; Thu, 24 Mar 1994 18:54:58 -0800
Resent-From: POSTGRES mailing list <postman@postgres.Berkeley.EDU>
Resent-Message-Id: <199403250254.SAA28896@nobozo.CS.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
X-Return-Path: owner-postman
Received: from faerie.CS.Berkeley.EDU (faerie.CS.Berkeley.EDU [128.32.149.14]) by nobozo.CS.Berkeley.EDU (8.6.4/8.6.3) with ESMTP id SAA28887 for <postgres@postgres.Berkeley.EDU>; Thu, 24 Mar 1994 18:54:58 -0800
Received: from localhost (localhost [127.0.0.1]) by faerie.CS.Berkeley.EDU (8.6.4/8.1B) with SMTP id SAA01790; Thu, 24 Mar 1994 18:53:51 -0800
Message-Id: <199403250253.SAA01790@faerie.CS.Berkeley.EDU>
X-Authentication-Warning: faerie.CS.Berkeley.EDU: Host localhost didn't use HELO protocol
From: aoki@postgres.Berkeley.EDU (Paul M. Aoki)
To: kskim@hyowon.pusan.ac.kr (kim kyongsok)
Cc: postgres@postgres.Berkeley.EDU, clcho@asadal.pusan.ac.kr,
        hwyun@hyowon.pusan.ac.kr, jhlee@asadal.pusan.ac.kr,
        yhkim@asadal.pusan.ac.kr, yjjeong@asadal.pusan.ac.kr
Subject: Re: index and # of tuples 
Reply-To: aoki@postgres.Berkeley.EDU (Paul M. Aoki)
In-reply-to: Your message of Fri, 25 Mar 1994 10:15:31 -1500 
	     <9403260115.AA43749@hyowon.pusan.ac.kr> 
Date: Thu, 24 Mar 94 18:53:51 -0800
X-Sender: aoki@postgres.Berkeley.EDU
Resent-To: postgres-dist@postgres.Berkeley.EDU
X-Mts: smtp
Resent-Date: Thu, 24 Mar 94 18:54:58 -0800
Resent-XMts: smtp

kskim@hyowon.pusan.ac.kr (kim kyongsok) writes:
> we defined a user-def data type, defined an index on a field of that type,
> and tested to see if the index is used.
> it seems that the index will be actually used (e.g., in retrieval)
> when the number of tuples exceeds some thereshold value 
> (probably for query opt.?).

exactly.  the postgres optimizer is based on the system r optimizer,
as described in:

%A P. G. Selinger
%A M. M. Astrahan
%A D. D. Chamberlin
%A R. A. Lorie
%A T. G. Price
%T Access Path Selection in a Relational Database Management System
%J PROC 1979 SIGMOD
%C Boston, MA
%D JUN 1979
%P 23-34

with extensions to handle things like inheritance/archive queries,
expensive user-defined functions, functional indices, user-defined
operators in join predicates, bushy query plan trees, etc.

it uses an estimate of the number of rows and disk blocks that the 
query will touch (as well as any user-specified ordering 
requirements) to decide between an index scan and a sequential scan.

if you don't vacuum regularly, the optimizer has no statistics and 
all plans cost the same.  you get random plans.  creating an index 
on a base table has the fortunate side effect of updating the 
statistics (row and block counts) for the base table.
--
  Paul M. Aoki  |  CS Div., Dept. of EECS, UCB  |  aoki@postgres.Berkeley.EDU
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki