Re: [HACKERS] Re: partial index

>
> > I had suspected that's what they were, but never really was sure.  Now
> > the next question, "Should we rip them out?"   No one uses them, and
> > they seem to be of very limited usefulness.
> >
> > I am inclinded to keep them, but I am not sure.
> >
> Do we have syntax for their creation and is it in the docs?
> If not I say just take them out, unless someone can think of a use that
> wouldn't be served by normal indexes.
>         -DEJ
>
>

    I can think of a situation where this is useful (even if very
    seldom).

    Have a table with many rows, indexed by a char(80) field.  In
    99%  of the selects the same 50 rows are searched (all having
    an 'A' as first character of the key).

    If you could only have these 50 in the index for fast access,
    the  complete  index  would  fit into a few blocks and can be
    searched faster.  All  other  rows  will  be  searched  by  a
    seqscan.

    Well,  there  are  limits  where  this all gets useless.  The
    speedup by having a small index (=faster index) is  eaten  up
    by  the longer time needed by seqscans very quickly. And it's
    a hard job to keep the predicates  for  the  partial  indices
    appropriate, so some overall speedup is gained.

    The possible speedup from this compared against the danger of
    having a dramatic slowdown on  the  other  side  is  a  clear
    drawback from my point of view.

    So  the only argument for having a partial index can be saved
    disk space. A bad argument when looking at the actual pricing
    of disks.

    Don't force it - use a bigger hammer!

    Result: Kick the partial indices out.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #