Re: SELECT * FROM <table> LIMIT 1; is really slow

On Fri, May 28, 2004 at 02:47:01PM -0400, Tom Lane wrote:

> We could possibly avoid this particular issue with sufficiently complex
> visibility rules. (I am thinking that we might be able to say that the
> inner xact can't see the tuple in question unless the creating command
> was "done" in the terms of the outer transaction, in which case perhaps
> we don't need its cmin anymore. But I fear that that won't work either.
> For instance a serializable cursor opened before the tuple was created
> should not be able to see it, so it sure seems like we need cmin.)
> And I don't feel confident that there are no other, even harder-to-avoid,
> cases to worry about.

Hm, the serializable cursor was the example I was looking for to show
why the current idea does not work.

> Something that just now occurred to me: could we identify
> subtransactions with commands? That is, cmin *is* the subtransaction
> ID, and xmin/xmax are always the parent xact? I'm not sure this works
> either, but it might be something to think about.

This seems a nice idea. We wouldn't need pg_subtrans at all, for
starters -- no multiple Xids for a transaction tree. And the cmin/cmax
test would only be done inside the backend running the xact tree, so it
doesn't need to be stored permanently, nor shared.

We would need to be able to mark individual CommandIds as aborted, and
while checking Cmin and Cmax, not only see how they compare to the
CurrentCommandId, but also whether they aborted.

It looks simpler to me than the current design.

The only problem would be _how_ to mark a bunch of CommandIds as
aborted -- keeping them all in memory seems too heavy. A bitmap could
be an interesting idea, but for a very big transaction we could need at
most 2^32 bits, which is way too much. Runlength encoding maybe? Any
graphic-library hacker around here with knowledge about compressing
bit strings? I know nothing of this stuff.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Si no sabes adonde vas, es muy probable que acabes en otra parte.