On Mon, 26 Apr 2004, Stephan Szabo wrote:
>
> On Fri, 23 Apr 2004 brad-pgperf@duttonbros.com wrote:
>
> > I have a query which I think should be using an index all of the time but
> > postgres only uses the index part of the time. The index
> > (ticket_crm_map_crm_id_suppid) has the where clause column (crm_id) listed
> > first followed by the selected column (support_person_id). Wouldn't the
> > most efficient plan be to scan the index each time because the only columns
> > needed are in the index? Below is the table, 2 queries showing the
>
> Not necessarily. The rows in the actual file still need to be checked to
> see if they're visible to the select and if it's expected that the entire
> file (or a reasonable % of the pages anyway) will need to be loaded using
> the index isn't necessarily a win.
While those of us familiar with PostgreSQL are well aware of the fact that
indexes can't be used directly to garner information, but only as a lookup
to a tuple in the table, it seems this misconception is quite common among
those coming to postgreSQL from other databases.
Is there any information that directly reflects this issue in the docs?
There are tons of hints that it works this way in how they're written, but
nothing that just comes out and says that with pgsql's mvcc
implementation, an index scan still has to hit the pages that contain the
tuples, so often in pgsql a seq scan is a win where in other databases and
index scan would have been a win?
If not, where would I add it if I were going to write something up for the
docs? Just wondering...
