Re: Index tuple killing code committed

Forgot to mention an important thing: I set enable_seqscan = off in
postgresql.conf.

> I found following mail in my mail archive and tried the same test with
> 7.4 current.  Contrary to my expectation 7.4 showed some performance
> degration with continuous pgbench runs:
> 
> $ pgbench -c 5 -t 1000 -n test
> tps = 57.444037 (including connections establishing)
> tps = 57.455300 (excluding connections establishing)
> 
> $ pgbench -c 5 -t 1000 -n test
> tps = 54.125785 (including connections establishing)
> tps = 54.134871 (excluding connections establishing)
> 
> $pgbench -c 5 -t 1000 -n test
> tps = 51.116465 (including connections establishing)
> tps = 51.124878 (excluding connections establishing)
> 
> $ pgbench -c 5 -t 1000 -n test
> tps = 50.410659 (including connections establishing)
> tps = 50.420215 (excluding connections establishing)
> 
> $ pgbench -c 5 -t 1000 test
> tps = 46.791980 (including connections establishing)
> tps = 46.799837 (excluding connections establishing)
> 
> Any idea?
> 
> data is initialized by pgbench -i -s 10.
> --
> Tatsuo Ishii
> 
> > From: Tom Lane <tgl@sss.pgh.pa.us>
> > To: pgsql-hackers@postgresql.org
> > Date: Fri, 24 May 2002 16:42:55 -0400
> >
> > 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
> > 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>