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

Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
> On Fri, May 28, 2004 at 02:47:01PM -0400, Tom Lane wrote:
>> 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.

> 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.

Not at all. In the first place, if there was anyone out there using
anything like 2^32 commands per xact, we'd have heard loud complaints
about the limit. ("My xact ran for a week, and then fell over because
of this stupid *%#*!*# limit! Fix it!") Since we've never ever heard
such a complaint, I think it's safe to assume there is plenty of
headroom there. I think ordinary cases are very unlikely to have more
than a few thousand commands per xact, and I don't think this will
change much with the availability of subtransactions. So I'd go for a
plain bitmap. (I *think* we only need one bit per CID, since we only
really care about "aborted or not" --- the visibility rules are simpler
than for concurrent xacts.)

We'd still need a plain CommandCounterIncrement facility, which means
that actually a subtransaction would have to be a group of CIDs not just
one. So there'd also need to be a data structure showing the CIDs
associated with each open subtransaction --- this is what you'd consult
to go and set the "aborted" bits if the subxact rolls back.

regards, tom lane