Re: Partial indexes Vs standard indexes : Insert

On Wed, 2006-08-16 at 12:15 +0200, MaXX wrote:
> > That should work. Keep in mind that the main idea of an index is to
> > reduce the number of pages that have to be fetched from disk. If the
> > record size is small, you may have at least one ICMP packet on 50% (or
> > more) of the disk pages even if ICMP packets only make up 1% of the
> > total records. Even if they aren't inserted randomly, updates/deletes
> > may randomize the distribution somewhat. If you have an ICMP packet on
> > every other page, you might not be impressed with the performance versus
> > a sequential scan. However, it could be a big win if you have other
> > WHERE conditions aside from just the packet type.
> OK, so that works well for queries where there is a very few rows in the
> index in regard of the table size, and as long as this still true.
>

Indexes work well when you have a WHERE clause that's highly restrictive
and reduces the number of pages needed from disk substantially. Partial
indexes work well when you're concerned about the index growing too
large (and requiring more maintenance), especially with keys you don't
need.

> > The planner tries to take all of these things into consideration to some
> > degree. The best test is to try EXPLAIN or EXPLAIN ANALYZE to see what
> > plan it makes. Also, try forcing different types of plans to see if the
> > planner is making the right choice.
> I did some test and with both your reply and the one of Gregory Stark, I
> was able identify what are good indexes and speed up the thing...
>

Excellent! Results are what count :)

Regards,
Jeff Davis