Re: pgsql is 75 times faster with my new index scan

devik(at)cdi(dot)cz wrote:
>
> Hello,
> I recently spoke about extending index scan to be able
> to take data directly from index pages. I wanted to know
> whether should I spend my time and implement it.
> So that I hacked last pgsql a bit to use proposed scan
> mode and did some measurements (see bellow). Measurements
> was done on (id int,txt varchar(20)) table with 1 000 000 rows
> with btree index on both attrs. Query involved was:
> select id,count(txt) from big group by id;
> Duplicates distribution on id column was 1:1000. I was run
> query twice after linux restart to ensure proper cache
> utilization (on disk heap & index was 90MB in total).
> So I think that by implementing this scan mode we can expect
> to gain huge speedup in all queries which uses indices and
> can found all data in their pages.
>
> Problems:
> my changes implemented only indexscan and new cost function.
> it doesn't work when index pages contains tuples which doesn't
> belong to our transaction. test was done after vacuum and
> only one tx running.
>
> TODO:
> - add HeapTupleHeaderData into each IndexTupleData
> - change code to reflect above
> - when deleting-updating heap then also update tuples'
> HeapTupleHeaderData in indices

I doubt everyone would like trading query speed for insert/update
speed plus index size

Perhaps this could be implemented as a special type of index which
you must explicitly specify at create time ?

> The last step could be done in two ways. First by limiting
> number of indices for one table we can store coresponding
> indices' TIDs in each heap tuple. The update is then simple
> taking one disk write.

Why limit it ? One could just save an tid array in each tuple .

-----------------
Hannu