Hi Jim,

> I'd like to see the performance numbers that show an improvement on some
> reproducible workload.

For example aggregate queries where many nodes needs to be visit :

SELECT
SUM(le.LEAFLENGTH),
SUM(le.LEAFWIDTH),
SUM(le.LEAFCAPACITY)
FROM
LEAFS l
JOIN LEAFS_EXTRAS le ON (le.ID = l.ID)
WHERE
l.ID between 100001 and 300000

PAGE_SIZE = 8192
BUFFERS=10000
OS=WIN2000
HDD=IDE 40GB 100MBS
LEAFS has a PRIMARY KEY ID INTEGER and some other inrelevant data-fields.
LEAFS_EXTRAS has a PRIMARY KEY (ID) INTEGER what is also an FOREIGN KEY to
LEAFS (ID) and some other inrelevant data-fields.


Current index structure :
-----------------------------------
[first time]
Current memory = 2755776
Delta memory = 422052
Max memory = 2826184
Elapsed time= 14.03 sec
Buffers = 10000
Reads = 5273
Writes 0
Fetches = 1400671
---
[second time]
Current memory = 3003324
Delta memory = 0
Max memory = 3364352
Elapsed time= 4.85 sec
Buffers = 10000
Reads = 0
Writes 0
Fetches = 1400369
-----------------------------------

New index structure :
-----------------------------------
[first time]
Current memory = 2757172
Delta memory = 423816
Max memory = 2825812
Elapsed time= 14.22 sec
Buffers = 10000
Reads = 5665
Writes 0
Fetches = 1601228
---
[second time]
Current memory = 2939952
Delta memory = 0
Max memory = 3300980
Elapsed time= 2.46 sec
Buffers = 10000
Reads = 0
Writes 0
Fetches = 1600926
-----------------------------------

> The problem I have with your analysis is that while your algorithm may be
> faster
> to find a given node on a page, your index keys are significantly larger
> than the
> existing scheme, leading to a less dense index and increased page reads.

Since

> a page read takes 10 to 20 milliseconds, a couple of microseconds saved
> scanning
> an index page doesn't really add up.

The orginal-index-walking seems to be more expensive then you expect (and
me) and it seems how bigger the page_size how slower the index-lookup. It
could be that of the B-tree design the page-read isn't that much more for
only 1 index-lookup.

Below the results of the same database but then with 1024 as page size and
current index structure :
-----------------------------------
Current memory = 3690000
Delta memory = 294512
Max memory = 3776344
Elapsed time= 2.99 sec
Buffers = 10000
Reads = 45762
Writes 0
Fetches = 1603022
-----------------------------------

> And since the system is almost always disk bound, I have a great deal of
> trouble
> understanding how decreasing CPU usage at the expense of additional disk
> traffic
> makes anything better. And I have even more trouble understanding how

making

> index pages less dense can increasing system throughput by 30-50%.

> So I'd like to see your performance numbers.

I hope this is the info what you wanted to see an maybe new conclusions /
ideas / comments. I don't say that the current index design is bad, but i
see a big performance lost with index-walking and i'm searching for how we
can improve this. (The main goal for the eventually new-index-structure
would be unique indexes where the key-length <= 8)

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird links :
http://www.firebirdsql.com
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/


Nederlandse firebird nieuwsgroep :
news://80.126.130.81