Thread

1. redundent index?

   Robert Treat <xzilla@users.sourceforge.net> — 2003-10-29T14:03:56Z

   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
   
   
   

2. Re: redundent index?

   Rod Taylor <rbt@rbt.ca> — 2003-10-29T15:17:24Z

   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.
   

3. Re: redundent index?

   Manfred Koizar <mkoi-pg@aon.at> — 2003-10-31T13:47:14Z

   On Wed, 29 Oct 2003 10:17:24 -0500, Rod Taylor <rbt@rbt.ca> 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