Re: [PERFORM] redundent index?
On Wed, 29 Oct 2003 10:17:24 -0500, Rod Taylor <[EMAIL PROTECTED]> wrote: >On Wed, 2003-10-29 at 09:03, Robert Treat wrote: >> Indexes: entity_watch_map_pkey primary key btree (entity_id, watch_id), >> ewm_entity_id btree (entity_id), >> >> I can't think of why the second index is there, as ISTM there is no >> instance where the first index wouldn't be used in place of the second > >The cost in evaluating the first index will be a little higher Yes, the actual cost may be a little higher. But the cost estimation might be significantly higher, so there can be border cases where the planner chooses a sequential scan over a multi-column index scan while a single-column index would correctly be recognized as being faster ... Servus Manfred ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] redundent index?
On Wed, 2003-10-29 at 09:03, Robert Treat wrote: > I just noticed on one of my tables I have the following two indexes: > > Indexes: entity_watch_map_pkey primary key btree (entity_id, watch_id), > ewm_entity_id btree (entity_id), > > > I can't think of why the second index is there, as ISTM there is no > instance where the first index wouldn't be used in place of the second The cost in evaluating the first index will be a little higher (more data to pull off disk due to second item), so there may be a few borderline cases that could switch to a sequential scan rather than an index scan. signature.asc Description: This is a digitally signed message part
[PERFORM] redundent index?
I just noticed on one of my tables I have the following two indexes: Indexes: entity_watch_map_pkey primary key btree (entity_id, watch_id), ewm_entity_id btree (entity_id), I can't think of why the second index is there, as ISTM there is no instance where the first index wouldn't be used in place of the second one if i were to delete the second one. its a heavily updated table, so axing the second one would be a bonus for performance, am i missing something? Thanks in advance, Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org