Jerome Bouvattier wrote:

>> ...
>>Could you also provide an extract of a gstat showing the number
>>of duplicates on each of these indexes?
>
>
>
> Here it is. It is not coming from the exact same db instance, but one
> showing the same problem anyway.
>
>
> PRESENCE (147)
> ...
> Index PRESENCE_IDX1 (1)
> Depth: 3, leaf buckets: 40233, nodes: 52989120
> Average data length: 0.00, total dup: 52989078, max dup: 1630178
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 2380
> 60 - 79% = 0
> 80 - 99% = 37853
>
> Index PRESENCE_IDX2 (2)
> Depth: 3, leaf buckets: 42772, nodes: 52989200
> Average data length: 0.00, total dup: 52971412, max dup: 2119727
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 7080
> 60 - 79% = 719
> 80 - 99% = 34973
>
>...
> Index RELATION_262 (7)
> Depth: 3, leaf buckets: 42315, nodes: 52989120
> Average data length: 0.00, total dup: 52983193, max dup: 116054
> Fill distribution:
> 0 - 19% = 2
> 20 - 39% = 0
> 40 - 59% = 5574
> 60 - 79% = 1494
> 80 - 99% = 35245
> ...

I didn't see an answer, so...

All of these may create problems (bad performance). A high value for 'max
dup' may make deletes (and updates) very slow.
Change the indexes to include the primary key, e.g.
create index PRESENCE_IDX2 on PRESENCE (SALEPRICE,ID);
This will reduce the 'max dup.' to 1!


--
Aage J.