Re: 8.3devel slower than 8.2 under read-only load

Tom,

On Nov 27, 2007 3:58 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Please try this patch on your real app (not the dumbed-down test case)
> and see what it does for you ...

If I disable the cache in the application, the most visited page
generates 175 SQL queries, mix of simple and more complicated queries
so it's quite "real life" (it's a page which aggregates quite a lot of
data - available in production here:
http://www.fra.cityvox.fr/guide_lyon/AccueilVille ).

The average of 5 runs for this page gives:
1.108 second for 8.2,
1.054 second for 8.3devel with your cache lookup patch.

Which gives us 8.3devel+cache patch a bit faster (~5%) than 8.2 on a
quite representative set of queries, even with no concurrency at all.
That's very good news.

Without your last patch but with the set of patches you commited these
last days (basically latest CVS tip), 8.3devel is nearly as fast as
8.2 to generate this page but consistently a bit slower.

For historical purposes, here are the results of my simple and dumb
test case (pgbench -c 10 -n 10000 with the custom queries I mentioned
earlier):
8.2: 1480 tps
2007-11-01: 1200 tps
2007-11-27: 1420 tps (after your set of patches to simplify the
planner for simple queries)
2007-11-27 + cache lookup patch: 2260 tps

That said, I checked nearly each of the 175 queries and I still find a
few queries a bit slower. I don't know if it's worth digging but as
you already found a couple of problems, I prefer mentioning it. For
example, after simplification of a query with a few joins which is
slower in 8.3devel+patch, I have the following results (consistent
over several runs of the query):
** 8.2 **
=> SELECT NP.codejour FROM newsplanning NP WHERE NP.codelang = 'FRA'
AND date_trunc('day',NP.dfin)>= date_trunc('day',now()) AND
date_trunc('day',NP.ddeb) <= date_trunc('day',now()) AND NP.codeth =
'THE' and NP.niveau = 1;
<snip 4 rows>
Time: 5.659 ms

=> EXPLAIN ANALYZE SELECT NP.codejour FROM newsplanning NP WHERE
NP.codelang = 'FRA' AND date_trunc('day',NP.dfin)>=
date_trunc('day',now()) AND date_trunc('day',NP.ddeb) <=
date_trunc('day',now()) AND NP.codeth = 'THE' and NP.niveau = 1;

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using
idx_newsplanning_numnews_codelang_dfin_ddeb_codeth_niveau on
newsplanning np (cost=0.00..752.95 rows=50 width=4) (actual
time=4.478..5.029 rows=4 loops=1)
Index Cond: (((codelang)::text = 'FRA'::text) AND ((codeth)::text =
'THE'::text) AND (niveau = 1))
Filter: ((date_trunc('day'::text, dfin) >= date_trunc('day'::text,
now())) AND (date_trunc('day'::text, ddeb) <= date_trunc('day'::text,
now())))
Total runtime: 5.064 ms

** 8.3devel with cache lookup **
=# SELECT NP.codejour FROM newsplanning NP WHERE NP.codelang = 'FRA'
AND date_trunc('day',NP.dfin)>= date_trunc('day',now()) AND
date_trunc('day',NP.ddeb) <= date_trunc('day',now()) AND NP.codeth =
'THE' and NP.niveau = 1;
<snip 4 rows>
Time: 5.932 ms

EXPLAIN ANALYZE SELECT NP.codejour FROM newsplanning NP WHERE
NP.codelang = 'FRA' AND date_trunc('day',NP.dfin)>=
date_trunc('day',now()) AND date_trunc('day',NP.ddeb) <=
date_trunc('day',now()) AND NP.codeth = 'THE' and NP.niveau = 1;

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using
idx_newsplanning_numnews_codelang_dfin_ddeb_codeth_niveau on
newsplanning np (cost=0.00..679.35 rows=46 width=4) (actual
time=4.884..5.467 rows=4 loops=1)
Index Cond: (((codelang)::text = 'FRA'::text) AND ((codeth)::text =
'THE'::text) AND (niveau = 1))
Filter: ((date_trunc('day'::text, dfin) >= date_trunc('day'::text,
now())) AND (date_trunc('day'::text, ddeb) <= date_trunc('day'::text,
now())))
Total runtime: 5.501 ms
(4 rows)

The schema of the table is the following:
=> \d newsplanning
Table "public.newsplanning"
Column | Type | Modifiers
---------------+-----------------------------+--------------------
idplanning | integer | not null
numnews | integer | not null
ddeb | timestamp without time zone | not null
dfin | timestamp without time zone | not null
codeth | character varying(3) | not null
niveau | integer | not null
ordre | integer |
codelang | character varying(3) | not null
ddermodif | timestamp without time zone |
logindermodif | character varying(20) |
codejour | integer | not null default 1
Indexes:
"pk_newsplanning" PRIMARY KEY, btree (idplanning)
"idx_newsplanning_ddeb_dfin_numnews_niveau_codelang" btree (ddeb,
dfin, numnews, niveau, codelang)
"idx_newsplanning_numnews_codelang_dfin_ddeb_codeth_niveau" btree
(numnews, codelang, dfin, ddeb, codeth, niveau)
Foreign-key constraints:
"fk_newsplanning_codejour" FOREIGN KEY (codejour) REFERENCES
jours(codejour) ON DELETE CASCADE
"fk_newsplanning_ref_codeth" FOREIGN KEY (codeth) REFERENCES
theme(codeth) ON DELETE CASCADE
"fk_newsplanning_ref_numnews" FOREIGN KEY (numnews) REFERENCES
news(numnews) ON DELETE CASCADE
"fk_newsplanning_ref_ord" FOREIGN KEY (niveau) REFERENCES
ordnews(ordnews) ON UPDATE RESTRICT ON DELETE RESTRICT

I can provide test data in private if needed.

--
Guillaume