Re: Index problems

On Wed, 3 Dec 2003, Ferdinand Smit wrote:

> Hi,
>
> When trying to explain a developer of our organisation the reson why the index
> was'nt used, i was confused my self.
>
> The simple question is: Why does the analyzer only use the index when the

There are a fiew things going on:

First, the statistics are overestimating the number of matching rows (by
say a factor of 3 in the first query).  You may wish to increase the
statistics target (alter table test alter column r set statistics <n>)
for something greater than 10, try 20 or 100 and re-analyze the table and
see if that lowers the estimated costs for the index scan.

Second, it's also possible that on your system random_page_cost should be
lower than 4.  Lowering that value lowers the estimated cost for index
scans.

Finally, it's also possible that the table is reasonably grouped by values
of r but that the statistics aren't realizing that fact. What does the row
in pg_statistic for that column show?