On 10/23/07, Heikki Linnakangas <heikki@enterprisedb.com> wrote: Gokulakannan Somasundaram wrote:
> Say, with a normal index, you need to goto the table for checking the
> snapshot. So you would be loading both the index pages + table pages, in
> order to satisfy a certain operations. Whereas in thick index you occupy 16
> bytes per tuple more in order to avoid going to the table. So memory
> management is again better. But i can run the load test, if that's
> required.
Yes, performance testing is required for any performance-related patch.
Remember that you're competing against DSM. We're going to want some
kind of a DSM anyway because it allows skipping unmodified parts of the
heap in vacuum.
> Even when all the tuples are in memory, index only scans are
> almost 40-60% faster than the index scans with thin indexes.
Have you actually benchmarked that? What kind of query was that? I don't
believe for a second that fetching the heap tuple when the page is in
memory accounts for 40-60% of the overhead of regular index scans.
The patch has been submitted. Try Explain Analyze. You can see it for yourself. Try creating a table and normal index. try creating another table with thick index. Check for queries which involves index-only scans. it won't get displayed in the plan. If you create a index on (n1,n2) and insert some 100,000 rows try querying like select n2 from table where n1 > and n1 <. Play around with it to see the difference.
> What do you thick about not maintaining pins in case of thick indexes?
Seems irrelevant. Keeping a page pinned is cheap.
I am not referring to the process of pinning a page. It is the occupation of 8KB of memory. you don't need to occupy it in case of thick indexes, once the page is referred.
BTW, another issue you'll have to tackle, that a DSM-based patch will
have to solve as well, is how to return tuples from an index. In b-tree,
we scan pages page at a time, keeping a list of all tids that match the
scanquals in BTScanOpaque. If we need to return not only the tids of the
matching tuples, but the tuples as well, where do we store them? You
could make a palloc'd copy of them all, but that seems quite expensive.
I have done a palloc for MinimalIndexTuple(with just the datums). palloc is costly, but it is not as costly as referring to a table. In other words it is not as costly as an I/O. Memory operates in micro seconds, I/O operates in milli seconds. I think the performance test results would answer these concerns.
Are you convinced with the update performance? Definitely that's not there with DSM....:)
Thanks,
Gokul.
CertoSQL Project.
Allied Solution Groups.
(
www.alliedgroups.com)