Subject: Re: index and # of tuples

From: [email-masked] (Paul M. Aoki)
Date: Thu, 24 Mar 94 18:53:51 -0800

[email-masked] (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  |  [email-masked]
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki

---

From: [email-masked] (kim kyongsok)
Date: Fri, 25 Mar 1994 10:15:31 -1500

is it the case?

thanks in advance.

k kim