Horton

Re: [PATCH] Caching for stable expressions with constant arguments v3

Marti Raudsepp <marti@juffo.org>

View thread
From: Marti Raudsepp <marti@juffo.org>
To: Greg Smith <greg@2ndquadrant.com>
Cc: Jaime Casanova <jaime@2ndquadrant.com>, pgsql-hackers@postgresql.org
Date: 2011-12-16T11:07:41Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. include_if_exists facility for config file.

Attachments

On Fri, Dec 16, 2011 at 09:13, Greg Smith <greg@2ndquadrant.com> wrote:
> I think what would be helpful here next is a self-contained benchmarking
> script.

Alright, a simple script is attached.

> One of the first questions I have is whether
> the performance changed between there and your v5.

Not those testcases anyway, since the executor side changed very
little since my original patch. I'm more worried about the planner, as
that's where the bulk of the code is. There is a small but measurable
regression there (hence the latter 2 tests).

I'm running with shared_buffers=256MB

Unpatched Postgres (git commit 6d09b210):

select * from ts where ts between to_timestamp('2005-01-01',
'YYYY-MM-DD') and to_timestamp('2005-01-01', 'YYYY-MM-DD');
tps = 1.194965 (excluding connections establishing)
tps = 1.187269 (excluding connections establishing)
tps = 1.192899 (excluding connections establishing)
select * from ts where ts>now();
tps = 8.986270 (excluding connections establishing)
tps = 8.974889 (excluding connections establishing)
tps = 8.976249 (excluding connections establishing)
/*uncachable*/ select * from one where ts >=
to_date(clock_timestamp()::date::text, 'YYYY-MM-DD') and ts <
(to_date(clock_timestamp()::date::text, 'YYYY-MM-DD') + interval '1
year')
tps = 11647.167712 (excluding connections establishing)
tps = 11836.858624 (excluding connections establishing)
tps = 11658.372658 (excluding connections establishing)
/*cachable*/ select * from one where ts >= to_date(now()::date::text,
'YYYY-MM-DD') and ts < (to_date(now()::date::text, 'YYYY-MM-DD') +
interval '1 year')
tps = 9762.035996 (excluding connections establishing)
tps = 9695.627270 (excluding connections establishing)
tps = 9791.141908 (excluding connections establishing)

Patched Postgres (v5 applied on top of above commit):

select * from ts where ts between to_timestamp('2005-01-01',
'YYYY-MM-DD') and to_timestamp('2005-01-01', 'YYYY-MM-DD');
tps = 8.580669 (excluding connections establishing)
tps = 8.583070 (excluding connections establishing)
tps = 8.544887 (excluding connections establishing)
select * from ts where ts>now();
tps = 10.467226 (excluding connections establishing)
tps = 10.429396 (excluding connections establishing)
tps = 10.441230 (excluding connections establishing)
/*uncachable*/ select * from one where ts >=
to_date(clock_timestamp()::date::text, 'YYYY-MM-DD') and ts <
(to_date(clock_timestamp()::date::text, 'YYYY-MM-DD') + interval '1
year')
tps = 11578.768193 (excluding connections establishing)
tps = 11594.920258 (excluding connections establishing)
tps = 11667.866443 (excluding connections establishing)
/*cachable*/ select * from one where ts >= to_date(now()::date::text,
'YYYY-MM-DD') and ts < (to_date(now()::date::text, 'YYYY-MM-DD') +
interval '1 year')
tps = 9505.943115 (excluding connections establishing)
tps = 9502.316023 (excluding connections establishing)
tps = 9546.047208 (excluding connections establishing)

Regards,
Marti