Обсуждение: Indices (was Re: [GENERAL] query seems too slow)

By the way,

I've created a secondary index on a large table in order to
gain some speed (the index is not unique), but the "explain"
did not report any change.

What else do I have to do to actually *use* an index? The manual
only tells how to create one.

FAQ?

Regards,
    Holger Klawitter
--
Holger Klawitter                                     +49 (0)251 484 0637
holger@klawitter.de                             http://www.klawitter.de/

On 1999-11-04, Holger Klawitter mentioned:

>
> By the way,
>
> I've created a secondary index on a large table in order to
> gain some speed (the index is not unique), but the "explain"
> did not report any change.
>
> What else do I have to do to actually *use* an index? The manual
> only tells how to create one.

In general, nothing.

But:
* Perhaps your table is not big enough so the optimizer doesn't deem the
  index worth its while.

* Perhaps you haven't run vacuum analyze lately, so the optimizer doesn't
  even know the table size and makes questionable decisions.

* Perhaps your query is formed in a way that disqualifies the use of
  indices.

* Perhaps the way you formed your query confuses the parser/optimizer.
  (There are several such "unfortunate circumstances"/bugs.)

* Finally: Perhaps the optimizer is really smarter than you.

> FAQ?

Definitely. Question 4.9.

    -Peter

--
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden