Re: [PATCHES] Including Snapshot Info with Indexes

On 10/23/07, Heikki Linnakangas <heikki(at)enterprisedb(dot)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)