svanderclock wrote:

> dear ann,
>
>> Post hoc, ergo proctor hoc? OK, lets continue with that assumption...
>
> meus latin est non valde bonus :)
>

But certainly you've studied logic!

OK, having taken a quick look at the table definition and gstat reports
below, I suggest you drop all the indexes where the max dup is more than
1/3 of the total. The purpose of an index is to avoid reading every
page in the table. If most keys in the index are present on every page,
then they index doesn't do any good. There is an exception, of course,
which is that you've got a table of, for example, Army generals, with
a field for gender and you only want to look at the women.

Another approach is to eliminate all the compound indexes and put
ascending and descending indexes on individual fields.

Basically, you're not going to get the performance you want unless
you reduce the ratio of indexes to data.

>
>
>>> also do you thing that the superserver could be better than the classic
>>> server on such bug? or i can wait firebird 2.5 (when it's will be
>> available) ?

Yes, I think you might get better results with superserver, just because
it can support a much larger cache, and you'll need a large cache to
add rows to all those indexes. You might try getting the MON$ stats
for an insert - fetches/reads/marks/fetches. It may be possible to
increase the cache size for the specific connection that's doing the
insert.

>
>> Are your bad nodes all level 1? Are any level 0?
>
> hmm, gfix don't say me where the bad node was, simply say me 69 index error found

The error in the log does tell you on which level it found the error.

>
>
> yes, i have
> 1/ the firebird.log

Good. The log includes the index level


Good luck,


Ann

> exemple of one of the 40 heavy table
>
> CREATE TABLE TRANSAC_SEARCH_RES_FS_A_2
> (
> ID_geo_place VARCHAR(100),
> ID_holder_agency VARCHAR(15) NOT NULL,
> ID_reader VARCHAR(15),
> Property_category SMALLINT NOT NULL,
> Advertisement_weight INTEGER NOT NULL,
> Creation_date TIMESTAMP NOT NULL,
> Total_sale_price NUMERIC(15,5),
> Surface NUMERIC(12,4),
> Nb_room SMALLINT,
> Nb_bedroom SMALLINT,
> ID_property VARCHAR(15) NOT NULL,
> ID_transac VARCHAR(15) NOT NULL
> );
>
> commit;
>
>
> /** single column **/
> CREATE ASCENDING INDEX TRANSAC_SEARCH_103_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_transac);
> CREATE ASCENDING INDEX TRANSAC_SEARCH_99_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (Total_sale_price);
> CREATE ASCENDING INDEX TRANSAC_SEARCH_100_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (Surface);
> CREATE ASCENDING INDEX TRANSAC_SEARCH_101_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (NB_room);
> CREATE ASCENDING INDEX TRANSAC_SEARCH_102_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (NB_bedroom);
>
> /** ID_geo_place, ID_reader, Transac_kind **/
> CREATE DESCENDING INDEX TRANSAC_SEARCH_104_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_reader, Advertisement_weight);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_105_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_reader, Creation_date);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_106_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_reader, Total_sale_price);
> CREATE ASCENDING INDEX TRANSAC_SEARCH_107_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_reader, Total_sale_price);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_108_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_reader, Surface);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_109_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_reader, NB_room);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_110_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_reader, NB_bedroom);
>
> /** ID_geo_place, ID_reader, Transac_kind, Property_category **/
> CREATE DESCENDING INDEX TRANSAC_SEARCH_111_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_reader, Property_category, Advertisement_weight);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_112_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_reader, Property_category, Creation_date);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_113_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_reader, Property_category, Total_sale_price);
> CREATE ASCENDING INDEX TRANSAC_SEARCH_114_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_reader, Property_category, Total_sale_price);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_115_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_reader, Property_category, Surface);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_116_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_reader, Property_category, NB_room);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_117_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_reader, Property_category, NB_bedroom);
>
> /** ID_geo_place, ID_holder_agency, ID_reader, Transac_kind **/
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1071_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_holder_agency, ID_reader, Advertisement_weight);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1072_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_holder_agency, ID_reader, Creation_date);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1073_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_holder_agency, ID_reader, Total_sale_price);
> CREATE ASCENDING INDEX TRANSAC_SEARCH_1074_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_holder_agency, ID_reader, Total_sale_price);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1075_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_holder_agency, ID_reader, Surface);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1076_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_holder_agency, ID_reader, NB_room);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1077_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_holder_agency, ID_reader, NB_bedroom);
>
> /** ID_geo_place, ID_holder_agency, ID_reader, Transac_kind, Property_category **/
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1080_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_holder_agency, ID_reader, Property_category, Advertisement_weight);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1081_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_holder_agency, ID_reader, Property_category, Creation_date);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1082_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_holder_agency, ID_reader, Property_category, Total_sale_price);
> CREATE ASCENDING INDEX TRANSAC_SEARCH_1083_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_holder_agency, ID_reader, Property_category, Total_sale_price);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1084_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_holder_agency, ID_reader, Property_category, Surface);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1085_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_holder_agency, ID_reader, Property_category, NB_room);
> CREATE DESCENDING INDEX TRANSAC_SEARCH_1086_IDX ON TRANSAC_SEARCH_RES_FS_A_2 (ID_geo_place, ID_holder_agency, ID_reader, Property_category, NB_bedroom);
>
> commit;
>
>
>
>
>
> TRANSAC_SEARCH_RES_FS_A_2 (276)
> Primary pointer page: 634, Index root page: 635
> Data pages: 182861, data page slots: 182861, average fill: 82%
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 928
> 80 - 99% = 181933
>
> Index TRANSAC_SEARCH_100_IDX (2)
> Depth: 3, leaf buckets: 5863, nodes: 5150351
> Average data length: 0.01, total dup: 5146112, max dup: 2235573
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 1
> 80 - 99% = 5862
>
> Index TRANSAC_SEARCH_101_IDX (3)
> Depth: 3, leaf buckets: 5703, nodes: 5150351
> Average data length: 0.00, total dup: 5150293, max dup: 2779803
> Fill distribution:
> 0 - 19% = 1
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 5702
>
> Index TRANSAC_SEARCH_102_IDX (4)
> Depth: 3, leaf buckets: 6229, nodes: 5150351
> Average data length: 0.00, total dup: 5150279, max dup: 1186843
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 1
> 60 - 79% = 0
> 80 - 99% = 6228
>
> Index TRANSAC_SEARCH_103_IDX (0)
> Depth: 3, leaf buckets: 6572, nodes: 5150351
> Average data length: 0.10, total dup: 4741612, max dup: 29
> Fill distribution:
> 0 - 19% = 1
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 6571
>
> Index TRANSAC_SEARCH_104_IDX (5)
> Depth: 3, leaf buckets: 10960, nodes: 5150351
> Average data length: 2.82, total dup: 31952, max dup: 3
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 1
> 80 - 99% = 10959
>
> Index TRANSAC_SEARCH_105_IDX (6)
> Depth: 3, leaf buckets: 11388, nodes: 5150351
> Average data length: 2.88, total dup: 554640, max dup: 139
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 1
> 80 - 99% = 11387
>
> Index TRANSAC_SEARCH_106_IDX (7)
> Depth: 3, leaf buckets: 12365, nodes: 5150351
> Average data length: 4.10, total dup: 3105504, max dup: 3330
> Fill distribution:
> 0 - 19% = 1
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 12364
>
> Index TRANSAC_SEARCH_1071_IDX (19)
> Depth: 3, leaf buckets: 11259, nodes: 5150351
> Average data length: 3.01, total dup: 27337, max dup: 3
> Fill distribution:
> 0 - 19% = 1
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 11258
>
> Index TRANSAC_SEARCH_1072_IDX (20)
> Depth: 3, leaf buckets: 11607, nodes: 5150351
> Average data length: 3.03, total dup: 554598, max dup: 139
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 1
> 80 - 99% = 11606
>
> Index TRANSAC_SEARCH_1073_IDX (21)
> Depth: 4, leaf buckets: 12884, nodes: 5150351
> Average data length: 4.45, total dup: 2989561, max dup: 1491
> Fill distribution:
> 0 - 19% = 1
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 12883
>
> Index TRANSAC_SEARCH_1074_IDX (22)
> Depth: 4, leaf buckets: 12879, nodes: 5150351
> Average data length: 4.45, total dup: 2989561, max dup: 1491
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 1
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 12878
>
> Index TRANSAC_SEARCH_1075_IDX (23)
> Depth: 3, leaf buckets: 9573, nodes: 5150351
> Average data length: 2.17, total dup: 4236157, max dup: 209400
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 1
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 9572
>
> Index TRANSAC_SEARCH_1076_IDX (24)
> Depth: 3, leaf buckets: 7195, nodes: 5150351
> Average data length: 0.52, total dup: 4816642, max dup: 209400
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 1
> 60 - 79% = 0
> 80 - 99% = 7194
>
> Index TRANSAC_SEARCH_1077_IDX (25)
> Depth: 3, leaf buckets: 7204, nodes: 5150351
> Average data length: 0.52, total dup: 4801561, max dup: 64520
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 1
> 60 - 79% = 0
> 80 - 99% = 7203
>
> Index TRANSAC_SEARCH_107_IDX (8)
> Depth: 3, leaf buckets: 12359, nodes: 5150351
> Average data length: 4.09, total dup: 3105504, max dup: 3330
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 1
> 60 - 79% = 0
> 80 - 99% = 12358
>
> Index TRANSAC_SEARCH_1080_IDX (26)
> Depth: 3, leaf buckets: 11910, nodes: 5150351
> Average data length: 3.42, total dup: 18655, max dup: 3
> Fill distribution:
> 0 - 19% = 1
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 11909
>
> Index TRANSAC_SEARCH_1081_IDX (27)
> Depth: 4, leaf buckets: 12194, nodes: 5150351
> Average data length: 3.44, total dup: 509528, max dup: 139
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 12194
>
> Index TRANSAC_SEARCH_1082_IDX (28)
> Depth: 4, leaf buckets: 13853, nodes: 5150351
> Average data length: 5.12, total dup: 2761426, max dup: 1167
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 13853
>
> Index TRANSAC_SEARCH_1083_IDX (29)
> Depth: 4, leaf buckets: 13847, nodes: 5150351
> Average data length: 5.12, total dup: 2761426, max dup: 1167
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 1
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 13846
>
> Index TRANSAC_SEARCH_1084_IDX (30)
> Depth: 4, leaf buckets: 10137, nodes: 5150351
> Average data length: 2.56, total dup: 4146854, max dup: 147372
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 1
> 60 - 79% = 0
> 80 - 99% = 10136
>
> Index TRANSAC_SEARCH_1085_IDX (31)
> Depth: 3, leaf buckets: 7540, nodes: 5150351
> Average data length: 0.75, total dup: 4756102, max dup: 147372
> Fill distribution:
> 0 - 19% = 1
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 7539
>
> Index TRANSAC_SEARCH_1086_IDX (32)
> Depth: 3, leaf buckets: 7550, nodes: 5150351
> Average data length: 0.76, total dup: 4733407, max dup: 48592
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 1
> 80 - 99% = 7549
>
> Index TRANSAC_SEARCH_108_IDX (9)
> Depth: 3, leaf buckets: 9102, nodes: 5150351
> Average data length: 1.85, total dup: 4339738, max dup: 216794
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 1
> 80 - 99% = 9101
>
> Index TRANSAC_SEARCH_109_IDX (10)
> Depth: 3, leaf buckets: 7005, nodes: 5150351
> Average data length: 0.39, total dup: 4849093, max dup: 245406
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 1
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 7004
>
> Index TRANSAC_SEARCH_110_IDX (11)
> Depth: 3, leaf buckets: 7013, nodes: 5150351
> Average data length: 0.40, total dup: 4831182, max dup: 101128
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 7013
>
> Index TRANSAC_SEARCH_111_IDX (12)
> Depth: 3, leaf buckets: 11578, nodes: 5150351
> Average data length: 3.21, total dup: 21140, max dup: 3
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 1
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 11577
>
> Index TRANSAC_SEARCH_112_IDX (13)
> Depth: 3, leaf buckets: 11928, nodes: 5150351
> Average data length: 3.26, total dup: 509547, max dup: 139
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 1
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 11927
>
> Index TRANSAC_SEARCH_113_IDX (14)
> Depth: 4, leaf buckets: 13279, nodes: 5150351
> Average data length: 4.73, total dup: 2879750, max dup: 1576
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 1
> 80 - 99% = 13278
>
> Index TRANSAC_SEARCH_114_IDX (15)
> Depth: 4, leaf buckets: 13274, nodes: 5150351
> Average data length: 4.73, total dup: 2879750, max dup: 1576
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 1
> 60 - 79% = 0
> 80 - 99% = 13273
>
> Index TRANSAC_SEARCH_115_IDX (16)
> Depth: 3, leaf buckets: 9607, nodes: 5150351
> Average data length: 2.20, total dup: 4255381, max dup: 149987
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 1
> 60 - 79% = 0
> 80 - 99% = 9606
>
> Index TRANSAC_SEARCH_116_IDX (17)
> Depth: 3, leaf buckets: 7305, nodes: 5150351
> Average data length: 0.60, total dup: 4792091, max dup: 165768
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 7305
>
> Index TRANSAC_SEARCH_117_IDX (18)
> Depth: 3, leaf buckets: 7316, nodes: 5150351
> Average data length: 0.60, total dup: 4767251, max dup: 75952
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 0
> 40 - 59% = 1
> 60 - 79% = 0
> 80 - 99% = 7315
>
> Index TRANSAC_SEARCH_99_IDX (1)
> Depth: 3, leaf buckets: 6519, nodes: 5150351
> Average data length: 0.07, total dup: 5103687, max dup: 37474
> Fill distribution:
> 0 - 19% = 1
> 20 - 39% = 0
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 6518
>
>