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(at)debis(dot)com (Jan Wieck) #