Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Manfred Koizar <mkoi-pg(at)aon(dot)at>
Cc: Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl>,David Blasby <dblasby(at)refractions(dot)net>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: SELECT * FROM <table> LIMIT 1; is really slow
Date: 2004-05-28 18:47:01
Message-ID: 29935.1085770021@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Manfred Koizar <mkoi-pg(at)aon(dot)at> writes:
> On Thu, 27 May 2004 16:50:24 -0400, Alvaro Herrera
>> At first I thought I'd have to add back Xmax as a field on its own

> Veto!  This would increase heap tuple header size ==> less tuples per
> page ==> more pages per table ==> more I/O ==> performance loss.

If putting back xmax is the price we must pay for nested transactions,
then we *will* pay that price.  Maybe not in this release, but it will
inevitably happen.  Don't bother hollering "veto" ;-)

I suspect that in fact this may be necessary.  The justification for
overlapping cmin and xmax hinges on two things:
	1. within the creating transaction, we can handle the
	   xmax=xmin case specially;
	2. another xact could only want to store xmax into a committed
	   tuple, therefore the original xact is done and we don't need
	   cmin anymore.
However this breaks down with nested xacts.  For instance imagine
this situation:

* Outer transaction creates a tuple.

* Inner transaction deletes this tuple (which it presumably can see).

* Inner transaction rolls back.

The tuple must still be visible to the outer xact.  However now we have
a problem: we've wiped out its cmin, which we need for visibility tests
in the outer xact.

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.

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.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2004-05-28 18:52:32
Subject: Re: Nested xacts: looking for testers and review
Previous:From: Manfred KoizarDate: 2004-05-28 18:29:51
Subject: Re: SELECT * FROM <table> LIMIT 1; is really slow

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group