pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
redundent index?
3+ messages / 3 participants
[nested] [flat]

* redundent index?
@ 2003-10-29 14:03  Robert Treat <xzilla@users.sourceforge.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Robert Treat @ 2003-10-29 14:03 UTC (permalink / raw)
  To: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: redundent index?
@ 2003-10-29 15:17  Rod Taylor <rbt@rbt.ca>
  parent: Robert Treat <xzilla@users.sourceforge.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Rod Taylor @ 2003-10-29 15:17 UTC (permalink / raw)
  To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: pgsql-performance

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.

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: redundent index?
@ 2003-10-31 13:47  Manfred Koizar <mkoi-pg@aon.at>
  parent: Rod Taylor <rbt@rbt.ca>
  0 siblings, 0 replies; 3+ messages in thread

From: Manfred Koizar @ 2003-10-31 13:47 UTC (permalink / raw)
  To: Rod Taylor <rbt@rbt.ca>; +Cc: Robert Treat <xzilla@users.sourceforge.net>; pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2003-10-31 13:47 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-29 14:03 redundent index? Robert Treat <xzilla@users.sourceforge.net>
2003-10-29 15:17 ` Rod Taylor <rbt@rbt.ca>
2003-10-31 13:47   ` Manfred Koizar <mkoi-pg@aon.at>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox