Please send your answer (also) to the list.
Le 28/11/2010 10:23, zhipan Wang a écrit :
> [...]
> Thanks a lot for your detailed reply. I am trying to implement the idea proposed
> in the paper Database Cracking. The basic idea is like this:
>
> When a query with a range predicate like "a<col" comes, a copy of the column col
> is created as a stand-alone table, and I need to partially sort (so physically
> reorganize) the table, and insert a node into the index to point to a tuple in
> the table, such that all values in the new table before that tuple are less
> than a and all values in col after that tuple are greater than or equal to a. As
> more and more queries come in, the table becomes better ordered, and a qeury can
> benefit from sequential scan enabled by this order.
>
Finding a solution to your issue is quite hard, because we don't know
which issue you have. Anyways, some thoughts...
PostgreSQL can already use an index to quickly sort a table. But CLUSTER
may be a more appropriate solution for you. You have to remember that
you'll need to launch CLUSTER from time to time so that the table get
reorganized.
> So the in-place update is a vital part of the program, because a range of values
> of column col should be stored together on the disk. Is there any way to do such
> a in-place update without generating much extra overhead?
>
If you need in-place update, then it's possible that PostgreSQL is not
the right tool for you.
But, as I already said, it's difficult to know for sure as we don't know
what issue you're trying to fix.
--
Guillaume
http://www.postgresql.fr
http://dalibo.com