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

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

From: Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Manfred Koizar <mkoi-pg(at)aon(dot)at>,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-29 01:16:33
Message-ID: 20040529011633.GB9271@dcc.uchile.cl (view raw or flat)
Thread:
Lists: pgsql-hackers
On Fri, May 28, 2004 at 04:45:28PM -0400, Tom Lane wrote:

> However, I just remembered why we rejected this idea to start with :-(.
> If we do it this way then when the overall xact commits, we no longer
> have state that tells which particular tuples are good or not.  We would
> have to trawl for tuples written by aborted subtransactions and mark
> them dead before committing, else other transactions would think they
> were good.
> 
> What this says is that we still need persistent pg_subtrans status.
> I'm not sure if we can use CIDs as subtrans IDs this way and still have
> a reasonably efficient storage representation for the global pg_subtrans
> table.

I'm not sure if I understand your last assertion.  We can in no way use
the CID as subtrans ID in pg_subtrans, of course (it starts from 0 at
each main transaction start).

So pg_subtrans remains the same, and we assign a new Xid to each
subtransaction.  Each tuple gets Xmin/Xmax according to the Xid of the
current subtransaction.  Within the transaction tree we don't use the
Xid to check for visibility, but Cmin/Cmax and the abort bitmap.

When the Xmin/xmax does not belong to our transaction tree, we use
pg_subtrans and pg_clog.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"We are who we choose to be", sang the goldfinch
when the sun is high (Sandman)


In response to

Responses

pgsql-hackers by date

Next:From: Sean ChittendenDate: 2004-05-29 01:20:10
Subject: Re: temp tables broken in CVS HEAD?
Previous:From: Alvaro HerreraDate: 2004-05-29 01:01:34
Subject: Re: Nested xacts: looking for testers and review

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