Re: Our CLUSTER implementation is pessimal

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>> Yeah, I've been thinking about how to use the planner to do this.
>
> I thought the answer to that was going to be more or less "call
> cost_sort() and cost_index() and compare the answers".

That was the way I was headed. But the idea of using the executor is looking
more and more attractive the more I think about it. It would solve this
problem as well as future-proof us against any other new features which could
speed up CLUSTER.

In particular I'm thinking of people clustering on a covering index (which
isn't as uncommon as it sounds, if you have a covering index you probably do
want to cluster it -- consider many-to-many join tables). We should be able to
do an index-only scan which might be even faster than sorting.

Also, it would cleanly solve the expression index case which my current
solution can't solve (not without much larger changes to tuplesort because the
sort key isn't present in the sort tuple). Also, if your table is very bloated
it could be faster to do a bitmap index scan and sort. Or scan another newer
and better organized index instead of the index you're clustering.

Basically I feel like what I've done is reproduce a small part of the planner
and executor and it would be a cleaner and more general solution to just do it
properly.

>> To do that it seems to me what we would need to do is add a function
>> _pg_get_rawtuple_header() which returns the visibility information that HTSV
>> needs.
>
> You seem to be confusing "use the planner" with "use the executor".
> All that we need here is a decision about which code path to take
> within CLUSTER. We don't need to bring in boatloads of irrelevant
> infrastructure --- especially not infrastructure that's going to be
> fighting us every step of the way. The executor isn't designed to
> return raw tuples and no magic function is going to change that.

Actually, thinking about it, couldn't we just a new system column to do this?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!