Re: UPDATEDs slowing SELECTs in a fully cached database

On 07/13/2011 11:42 AM, Kevin Grittner wrote:
> So transactions without an XID *are* sensitive to
> synchronous_commit.  That's likely a useful clue.
>
> How much did it help the run time of the SELECT which followed the
> UPDATE?

It has surprisingly little impact on the SELECT side:

=> set synchronous_commit = on;
=> update test set created_by = '000000000000001' where tenant =
'000000000000001';
UPDATE 3712
Time: 384.702 ms
lars=> select count(*) from test where tenant = '000000000000001' and
created_date = '2011-6-30';
  count
-------
   3712
(1 row)

Time: 36.571 ms
=> select count(*) from test where tenant = '000000000000001' and
created_date = '2011-6-30';
  count
-------
   3712
(1 row)

Time: 5.702 ms
=> select count(*) from test where tenant = '000000000000001' and
created_date = '2011-6-30';
  count
-------
   3712
(1 row)

Time: 5.822 ms
=> set synchronous_commit = off;
SET
Time: 0.145 ms
=> update test set created_by = '000000000000001' where tenant =
'000000000000001';
UPDATE 3712
Time: 96.227 ms
=> select count(*) from test where tenant = '000000000000001' and
created_date = '2011-6-30';
  count
-------
   3712
(1 row)

Time: 32.422 ms
=> select count(*) from test where tenant = '000000000000001' and
created_date = '2011-6-30';
  count
-------
   3712
(1 row)

Time: 6.080 ms

I tried it multiple times, and while the numbers change by 5-10ms the
relationship is the same.

The same results show when I use my JDBC code to run updates/selects as
fast as possible. When synchronous_commit is
off for the SELECTing process it seems to be slightly faster.

-- Lars