On Sat, 2002-05-25 at 02:38, Joe Conway wrote:
> Tom Lane wrote:
> > The remaining degradation is actually in seqscan performance, not
> > indexscan --- unless one uses a much larger -s setting, the planner will
> > think it ought to use seqscans for updating the "branches" and "tellers"
> > tables, since those nominally have just a few rows; and there's no way
> > to avoid scanning lots of dead tuples in a seqscan.  Forcing indexscans
> > helps some in the former CVS tip:
> > 
> 
> This may qualify as a "way out there" idea, or more trouble than it's 
> worth, but what about a table option which provides a bitmap index of 
> tuple status -- i.e. tuple dead t/f. If available, a seqscan in between 
> vacuums could maybe gain some of the same efficiency.

I guess this would be only useful if it is a bitmap of dead _pages_ not
tuples (page reading is mostexpensive plus there is no way to know how
many tuples per page)

but for worst cases (small table with lots of updates) this can be a
great thing that can postpone fixing optimiser to account for dead
tuples.

one 8K pages can hold bits for 8192*8 = 65536 pages = 512 Mbytes and if
seqscan could skip first 500 of them it would definitely be worth it ;)

> > This is the first time I have ever seen repeated pgbench runs without
> > substantial performance degradation.  Not a bad result for a Friday
> > afternoon...

Really good news!

-----------
Hannu



25 May 2002 00:49:39 +0500
Hannu Krosing <hannu@tm.ee>
Re: Index tuple killing code committed
Per previous discussion, I have committed changes that cause the btree
and hash index methods to mark index tuples "killed" the first time they
are fetched after becoming globally dead.  Subsequently the killed
entries are not returned out of indexscans, saving useless heap fetches.
(I haven't changed rtree and gist yet; they will need some internal
restructuring to do this efficiently.  Perhaps Oleg or Teodor would like
to take that on.)

This seems to make a useful improvement in pgbench results.  Yesterday's
CVS tip gave me these results:

(Running postmaster with "-i -F -B 1024", other parameters at defaults,
and pgbench initialized with "pgbench -i -s 10 bench")

$ time pgbench -c 5 -t 1000 -n bench
tps = 26.428787(including connections establishing)
tps = 26.443410(excluding connections establishing)
real    3:09.74
$ time pgbench -c 5 -t 1000 -n bench
tps = 18.838304(including connections establishing)
tps = 18.846281(excluding connections establishing)
real    4:26.41
$ time pgbench -c 5 -t 1000 -n bench
tps = 13.541641(including connections establishing)
tps = 13.545646(excluding connections establishing)
real    6:10.19

Note the "-n" switches here to prevent vacuums between runs; the point
is to observe the degradation as more and more dead tuples accumulate.

With the just-committed changes I get (starting from a fresh start):

$ time pgbench -c 5 -t 1000 -n bench
tps = 28.393271(including connections establishing)
tps = 28.410117(excluding connections establishing)
real    2:56.53
$ time pgbench -c 5 -t 1000 -n bench
tps = 23.498645(including connections establishing)
tps = 23.510134(excluding connections establishing)
real    3:33.89
$ time pgbench -c 5 -t 1000 -n bench
tps = 18.773239(including connections establishing)
tps = 18.780936(excluding connections establishing)
real    4:26.84

The remaining degradation is actually in seqscan performance, not
indexscan --- unless one uses a much larger -s setting, the planner will
think it ought to use seqscans for updating the "branches" and "tellers"
tables, since those nominally have just a few rows; and there's no way
to avoid scanning lots of dead tuples in a seqscan.  Forcing indexscans
helps some in the former CVS tip:

$ PGOPTIONS="-fs" time pgbench -c 5 -t 1000 -n bench
tps = 28.840678(including connections establishing)
tps = 28.857442(excluding connections establishing)
real     2:53.9
$ PGOPTIONS="-fs" time pgbench -c 5 -t 1000 -n bench
tps = 25.670674(including connections establishing)
tps = 25.684493(excluding connections establishing)
real     3:15.7
$ PGOPTIONS="-fs" time pgbench -c 5 -t 1000 -n bench
tps = 22.593429(including connections establishing)
tps = 22.603928(excluding connections establishing)
real     3:42.7

and with the changes I get:

$ PGOPTIONS=-fs time pgbench -c 5 -t 1000 -n bench
tps = 29.445004(including connections establishing)
tps = 29.463948(excluding connections establishing)
real     2:50.3
$ PGOPTIONS=-fs time pgbench -c 5 -t 1000 -n bench
tps = 30.277968(including connections establishing)
tps = 30.301363(excluding connections establishing)
real     2:45.6
$ PGOPTIONS=-fs time pgbench -c 5 -t 1000 -n bench
tps = 30.209377(including connections establishing)
tps = 30.230646(excluding connections establishing)
real     2:46.0


This is the first time I have ever seen repeated pgbench runs without
substantial performance degradation.  Not a bad result for a Friday
afternoon...

			regards, tom lane

Fri, 24 May 2002 16:42:55 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Index tuple killing code committed
Tom Lane wrote:
> The remaining degradation is actually in seqscan performance, not
> indexscan --- unless one uses a much larger -s setting, the planner will
> think it ought to use seqscans for updating the "branches" and "tellers"
> tables, since those nominally have just a few rows; and there's no way
> to avoid scanning lots of dead tuples in a seqscan.  Forcing indexscans
> helps some in the former CVS tip:
> 

This may qualify as a "way out there" idea, or more trouble than it's 
worth, but what about a table option which provides a bitmap index of 
tuple status -- i.e. tuple dead t/f. If available, a seqscan in between 
vacuums could maybe gain some of the same efficiency.

> This is the first time I have ever seen repeated pgbench runs without
> substantial performance degradation.  Not a bad result for a Friday
> afternoon...

Nice work!

Joe


Fri, 24 May 2002 14:38:41 -0700
Joe Conway <mail@joeconway.com>
Re: Index tuple killing code committed
Joe Conway <mail@joeconway.com> writes:
> This may qualify as a "way out there" idea, or more trouble than it's 
> worth, but what about a table option which provides a bitmap index of 
> tuple status -- i.e. tuple dead t/f. If available, a seqscan in between 
> vacuums could maybe gain some of the same efficiency.

Hmm.  I'm inclined to think that a separate bitmap index wouldn't be
worth the trouble.  Under most scenarios it'd just require extra I/O
and not buy much.

However ... we could potentially take over the LP_DELETED flag bit of
heap tuples for the same use as for index tuples: set it when the tuple
is known dead for all transactions.  This would save calling
HeapTupleSatisfiesSnapshot in the inner loop of heapgettup, while not
adding much expense for the normal case where the tuple's not dead.

			regards, tom lane

Fri, 24 May 2002 18:09:06 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Index tuple killing code committed 