Tom Lane wrote:
> "Merlin Moncure" <[EMAIL PROTECTED]> writes:
> > I took advantage of the holidays to update a production server (dual
> > Opteron on win2k) from an 11/16 build (about beta5 or so) to the
> > release candidate.  No configuration changes were made, just a
> > swap and a server stop/start.
> > I was shocked to see that statement latency dropped by a fairly
> > margin.
> Hmm ... I trawled through the CVS logs since 11/16, and did not see
> many changes that looked like they might improve performance (list
> attached) --- and even of those, hardly any looked like the change
> be significant.  Do you know whether the query plans changed?  Are you
> running few enough queries per connection that backend startup
> might be an issue?

No, everything is run over persistent connections and prepared
statements.  All queries boil down to an index scan of some sort, so the
planner is not really a factor.  It's all strictly execution times, and
data is almost always read right off of the cache.  The performance of
the ISAM driver is driven by 3 factors (in order).
1. network latency (including o/s overhead context switches, etc.)
2. i/o factors (data read from cache, disk, etc).
3. overhead for pg to execute trivial transaction.

#1 & #2 are well understood problems.  It's #3 that improved
substantially and without warning.  See my comments below:

>                       regards, tom lane
> 2004-12-15 14:16  tgl
>       * src/backend/access/nbtree/nbtutils.c: Calculation of
>       keys_are_unique flag was wrong for cases involving redundant
>       cross-datatype comparisons.  Per example from Merlin Moncure.

Not likely to have a performance benefit.
> 2004-12-02 10:32  momjian
>       * configure,, doc/src/sgml/libpq.sgml,
>       doc/src/sgml/ref/copy.sgml, src/interfaces/libpq/fe-connect.c,
>       src/interfaces/libpq/fe-print.c,
>       src/interfaces/libpq/libpq-fe.h,
>       Rework libpq threaded SIGPIPE handling to avoid interference
>       calling applications.  This is done by blocking sigpipe in the
>       libpq thread and using sigpending/sigwait to possibily discard
>       sigpipe we generated.

> 2004-12-01 20:34  tgl
>       * src/: backend/optimizer/path/costsize.c,
>       backend/optimizer/util/plancat.c,
>       test/regress/expected/geometry.out,
>       test/regress/expected/geometry_1.out,
>       test/regress/expected/geometry_2.out,
>       test/regress/expected/inherit.out,
>       test/regress/sql/inherit.sql, test/regress/sql/join.sql: Make
>       adjustments to reduce platform dependencies in plan selection.
>       particular, there was a mathematical tie between the two
>       nestloop-with-materialized-inner-scan plans for a join (ie, we
>       computed the same cost with either input on the inside),
>       in a roundoff error driven choice, if the relations were both
>       enough to fit in sort_mem.  Add a small cost factor to ensure we
>       prefer materializing the smaller input.  This changes several
>       regression test plans, but with any luck we will now have more
>       stability across platforms.

No.  The planner is not a factor.
> 2004-12-01 14:00  tgl
>       * doc/src/sgml/catalogs.sgml, doc/src/sgml/diskusage.sgml,
>       doc/src/sgml/perform.sgml, doc/src/sgml/release.sgml,
>       src/backend/access/nbtree/nbtree.c, src/backend/catalog/heap.c,
>       src/backend/catalog/index.c, src/backend/commands/vacuum.c,
>       src/backend/commands/vacuumlazy.c,
>       src/backend/optimizer/util/plancat.c,
>       src/backend/optimizer/util/relnode.c,
>       src/include/nodes/relation.h,
>       src/test/regress/expected/inherit.out,
>       src/test/regress/expected/join.out,
>       src/test/regress/expected/join_1.out,
>       src/test/regress/expected/polymorphism.out: Change planner to
>       the current true disk file size as its estimate of a relation's
>       number of blocks, rather than the possibly-obsolete value in
>       pg_class.relpages.  Scale the value in pg_class.reltuples
>       correspondingly to arrive at a hopefully more accurate number of
>       rows.  When pg_class contains 0/0, estimate a tuple width from
>       column datatypes and divide that into current file size to
>       number of rows.  This improved methodology allows us to jettison
>       the ancient hacks that put bogus default values into pg_class
>       a table is first created.  Also, per a suggestion from Simon,
>       VACUUM (but not VACUUM FULL or ANALYZE) adjust the value it puts
>       into pg_class.reltuples to try to represent the mean tuple
>       instead of the minimal density that actually prevails just after
>       VACUUM.  These changes alter the plans selected for certain
>       regression tests, so update the expected files accordingly.  (I
>       removed join_1.out because it's not clear if it still applies;
>       can add back any variant versions as they are shown to be

doesn't seem like this would apply.
> 2004-11-21 17:57  tgl
>       * src/backend/utils/hash/dynahash.c: Fix rounding problem in
>       dynahash.c's decision about when the target fill factor has been
>       exceeded.  We usually run with ffactor == 1, but the way the
>       was coded, it wouldn't split a bucket until the actual fill
>       reached 2.0, because of use of integer division.  Change from >
>       >= so that it will split more aggressively when the table starts
>       get full.

Hmm. Not likely.
> 2004-11-21 17:48  tgl
>       * src/backend/utils/mmgr/portalmem.c: Reduce the default size of
>       the PortalHashTable in order to save a few cycles during
>       transaction exit.  A typical session probably wouldn't have as
>       as half a dozen portals open at once, so the original value of
>       seems far larger than needed.

Strong possibility...'few cycles' seems pretty small tho :).
> 2004-11-20 15:19  tgl
>       * src/backend/utils/cache/relcache.c: Avoid scanning the
>       during AtEOSubXact_RelationCache when there is nothing to do,
>       is most of the time.  This is another simple improvement to cut
>       subtransaction entry/exit overhead.

Not clear from the comments: does this apply to every transaction, or
only ones with savepoints?  If all transactions, it's a contender.
> 2004-11-20 15:16  tgl
>       * src/backend/storage/lmgr/lock.c: Reduce the default size of
>       local lock hash table.  There's usually no need for it to be
>       as big as the global hash table, and since it's not in shared
>       memory it can grow if it does need to be bigger.  By reducing
>       size, we speed up hash_seq_search(), which saves a significant
>       fraction of subtransaction entry/exit overhead.

Same comments as above.

> 2004-11-19 19:48  tgl
>       * src/backend/tcop/postgres.c: Move pgstat_report_tabstat() call
>       that stats are not reported to the collector until the
>       commits.  Per recent discussion, this should avoid confusing
>       autovacuum when an updating transaction runs for a long time.

Not likely.
> 2004-11-16 22:13  neilc
>       * src/backend/access/: hash/hash.c, nbtree/nbtree.c:
>       Micro-optimization of markpos() and restrpos() in btree and hash
>       indexes.  Rather than using ReadBuffer() to increment the
>       count on an already-pinned buffer, we should use
>       IncrBufferRefCount() as it is faster and does not require
>       the BufMgrLock.

Another contender...maybe the cost of acquiring the lock is higher on
some platforms than others.
> 2004-11-16 19:14  tgl
>       * src/: backend/main/main.c, backend/port/win32/signal.c,
>       backend/postmaster/pgstat.c, backend/postmaster/postmaster.c,
>       include/port/win32.h: Fix Win32 problems with signals and
>       by making the forkexec code even uglier than it was already :-(.
>       Also, on Windows only, use temporary shared memory segments
>       of ordinary files to pass over critical variable values from
>       postmaster to child processes.  Magnus Hagander

As I understand it, this only affects backend startup time, so, no.
I'll benchmark some more until I get a better answer.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?


Reply via email to