----- Original Message -----
From: "Ann W. Harrison" <aharrison@...>
To: <firebird-support@yahoogroups.com>; <firebird-support@yahoogroups.com>
Sent: Tuesday, August 31, 2004 9:44 PM
Subject: Re: [firebird-support] Big table and DB slowdowns


> At 11:24 AM 8/31/2004, Jerome Bouvattier wrote:
>
>
> >You will find hereafter some more tech infos. Let me know if I should
> >provide more.
> >
> >
> >Selectivities:
> >
> >
> >PK_PRESENCE 1.887221401375427e-008
> >PRESENCE_IDX1 0.02380952425301075
> >PRESENCE_IDX2 5.623980541713536e-005
> >PRESENCE_IDX3 7.386178822343936e-006
> >PRESENCE_IDX4 1.887221401375427e-008
> >PRESENCE_IDX5 1.601524388661346e-007
> >RELATION_262 0.0001687478943495080
> >RELATION_296 2.158489223802462e-006
>
> 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)
Primary pointer page: 181, Index root page: 182
Data pages: 557777, data page slots: 557777, average fill: 72%
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 557776
80 - 99% = 0

Index PK_PRESENCE (0)
Depth: 3, leaf buckets: 86562, nodes: 52989120
Average data length: 1.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 81351
60 - 79% = 0
80 - 99% = 5211

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 PRESENCE_IDX3 (4)
Depth: 3, leaf buckets: 42402, nodes: 52989121
Average data length: 0.00, total dup: 52853677, max dup: 3375
Fill distribution:
0 - 19% = 3
20 - 39% = 1
40 - 59% = 5564
60 - 79% = 1499
80 - 99% = 35335

Index PRESENCE_IDX4 (5)
Depth: 4, leaf buckets: 104639, nodes: 52989121
Average data length: 2.00, total dup: 1, max dup: 1
Fill distribution:
0 - 19% = 38
20 - 39% = 0
40 - 59% = 91613
60 - 79% = 7182
80 - 99% = 5806

Index PRESENCE_IDX5 (6)
Depth: 3, leaf buckets: 75954, nodes: 52989121
Average data length: 0.00, total dup: 46742046, max dup: 370
Fill distribution:
0 - 19% = 24
20 - 39% = 1
40 - 59% = 66767
60 - 79% = 2202
80 - 99% = 6960

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

Index RELATION_296 (3)
Depth: 3, leaf buckets: 68766, nodes: 52989120
Average data length: 0.00, total dup: 52525761, max dup: 9531
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 59515
60 - 79% = 2058
80 - 99% = 7192

--
Jerome