Re: HOT patch - version 15 - Mailing list pgsql-patches

Bruce Momjian wrote:
> (Can someone time the access time for following a chain that fills an
> entire page (the worst case) vs. having a single tuple on the page?)

Here is some results, on my laptop. The test case is a table with a
single integer column, and a single row in the table. The row is updated
250 times to make a 251 tuples long chain. Page can hold 255 tuples, so
this is pretty much the worst case. After that, the row is fetched
1000000 times, in a PL/pgSQL function. I ran the tests with
enable_seqscan on and off, see "seqscan" and "idxscan" rows below.
Numbers are the times spent doing the fetches, in seconds. I repeated
each test a few times to make sure that the results are repeatable, they
seem to be repeatable to ~0.1s precision. The test script used is
attached. Autovacuum was disabled, and shared_buffers=320MB, otherwise
all settings were left to defaults.

        HEAD    HOT    HOT-opt    HOT-pruned
seqscan        19.9    21.1    20.1    11.5
idxscan        27.8    31.4    30.4    13.7

Explanations of the columns:

HEAD:        CVS HEAD
HOT-pruned:    CVS HEAD + HOT patch v15
HOT:        CVS HEAD + HOT patch v15, but with heap_page_prune_defrag short
circuited to do nothing
HOT-opt:    CVS HEAD + HOT patch v15, but with static XidInMVCCSnapshot
like in CVS HEAD

I didn't expect a difference in seqscan performance between HEAD and
HOT. I oprofiled it, and figured out that it's because HOT patch removed
the static-qualifier XidInMVCCSnapshot, because it's needed in
plancat.c. I changed it back to static, dummying out the call in
plancat.c, and the results are now closer to each other (HOT-opt column).

Comparing the idxscan columns, it looks like following the chain *is*
more expensive than having to go through killed index pointers. Pruning
clearly does help.

Given that this test is pretty much the worst case scenario, I'm ok with
not pruning for the purpose of keeping chains short.

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com
set enable_seqscan=on;

DROP TABLE hottest;

CREATE TABLE hottest (id integer);

INSERT INTO hottest VALUES (1);

CREATE INDEX i_hottest ON hottest(id);

-- Fill the table

CREATE OR REPLACE FUNCTION longchain (n integer) RETURNS void LANGUAGE PLPGSQL AS
$$
DECLARE
  i integer;
BEGIN
  FOR i IN 1..n LOOP
    UPDATE hottest SET id=id WHERE id = 1;
  END LOOP;
END;
$$;

SELECT longchain(250);

CREATE OR REPLACE FUNCTION fetchchain (n integer) RETURNS void LANGUAGE PLPGSQL AS
$$
DECLARE
  i integer;
  foo integer;
BEGIN
  FOR i IN 1..n LOOP
    SELECT id INTO foo FROM hottest WHERE id = 1;
  END LOOP;
END;
$$;

\timing
SELECT fetchchain(1000000);
\timing