pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed8.2rc1 (much) slower than 8.2dev?
7+ messages / 3 participants
[nested] [flat]
* 8.2rc1 (much) slower than 8.2dev?
@ 2006-12-04 07:44 Arjen van der Meijden <acmmailing@tweakers.net>
0 siblings, 1 reply; 7+ messages in thread
From: Arjen van der Meijden @ 2006-12-04 07:44 UTC (permalink / raw)
To: pgsql-performance
Hi List,
We've been doing some benchmarks lately (one of them made it to the
PostgreSQL frontpage) with postgresql 8.2 dev (cvs checkout of 3 june
2006). But we prefer of course to run a more production-like version and
installed postgresql 8.2rc1.
As it turns out after a dump/restore (to go from 820 to 822), copying
the configuration files doing a fresh 'vacuumdb -z' (z is analyze) and
'clusterdb' the RC1 processes about 50% *less* (webpage)requests than
the 8.2dev we had, on the same machine/linux kernel/etc. On all
cpu-configurations and loads we throw at it. Since its a read-mostly
database the location on disk should matter only very slightly.
For instance, with the system currently at hand it peaks at about 20
concurrent clients in pg8.2 dev with 465407 requests processed in a 10
minuten timeframe. 8.2rc1 can only achieve 332499 requests in that same
time frame with the same load and has a peak of 335995 with 35
concurrent clients (but with 30 it only reached 287624). And we see the
same for all loads we throw at them.
So either I'm missing something, there is a (significant enough)
difference in how the tables where analyzed or there have been some
code-changes since then to change the behaviour and thereby decreasing
performance in our set-up.
Preferably I'd load the statistics from the 8.2-dev database in the
8.2-rc1 one, but a simple insert or copy-statement won't work due to the
'anyarray'-fields of pg_statistic, will it?
I'll run another analyze on the database to see if that makes any
difference, but after that I'm not sure what to check first to figure
out where things go wrong?
Best regards,
Arjen van der Meijden
Tweakers.net
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: 8.2rc1 (much) slower than 8.2dev?
@ 2006-12-04 14:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Arjen van der Meijden <acmmailing@tweakers.net>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2006-12-04 14:53 UTC (permalink / raw)
To: Arjen van der Meijden <acmmailing@tweakers.net>; +Cc: pgsql-performance
Arjen van der Meijden <acmmailing@tweakers.net> writes:
> I'll run another analyze on the database to see if that makes any
> difference, but after that I'm not sure what to check first to figure
> out where things go wrong?
Look for changes in plans?
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: 8.2rc1 (much) slower than 8.2dev?
@ 2006-12-04 16:41 Arjen van der Meijden <acmmailing@tweakers.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 7+ messages in thread
From: Arjen van der Meijden @ 2006-12-04 16:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane wrote:
> Arjen van der Meijden <acmmailing@tweakers.net> writes:
>> I'll run another analyze on the database to see if that makes any
>> difference, but after that I'm not sure what to check first to figure
>> out where things go wrong?
>
> Look for changes in plans?
Yeah, there are a few number of small changes in plans and costs
estimated. I've a large list of queries executed against both databases,
and I haven't seen any differences in row-estimates, so the analyze's
have yielded similar enough results.
I'm not sure whether some of the changes are for better or worse, you
can probably spot that a bit faster than I can. I saw a few index scans
replaced by seq scans (on small tables), all index scans seem to have
doubled in cost? And I saw a few bitmap scans in stead of normal index
scans and more such small changes. But not so small if you execute a
hundreds of thousands of those queries.
Since I'd rather not send the entire list of queries to the entire
world, is it OK to send both explain analyze-files to you off list?
Best regards,
Arjen
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: 8.2rc1 (much) slower than 8.2dev?
@ 2006-12-07 06:01 Jim C. Nasby <jim@nasby.net>
parent: Arjen van der Meijden <acmmailing@tweakers.net>
1 sibling, 1 reply; 7+ messages in thread
From: Jim C. Nasby @ 2006-12-07 06:01 UTC (permalink / raw)
To: Arjen van der Meijden <acmmailing@tweakers.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Mon, Dec 04, 2006 at 05:41:14PM +0100, Arjen van der Meijden wrote:
> Since I'd rather not send the entire list of queries to the entire
> world, is it OK to send both explain analyze-files to you off list?
Can you post them on the web somewhere so everyone can look at them?
Also, are you looking at EXPLAIN or EXPLAIN ANALYZE?
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: 8.2rc1 (much) slower than 8.2dev?
@ 2006-12-07 09:13 Arjen van der Meijden <acmmailing@tweakers.net>
parent: Jim C. Nasby <jim@nasby.net>
0 siblings, 1 reply; 7+ messages in thread
From: Arjen van der Meijden @ 2006-12-07 09:13 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On 7-12-2006 7:01 Jim C. Nasby wrote:
> Can you post them on the web somewhere so everyone can look at them?
No, its not (only) the size that matters, its the confidentiality I'm
not allowed to just break by myself. Well, at least not on a scale like
that. I've been mailing off-list with Tom and we found at least one
query that in some circumstances takes a lot more time than it should,
due to it mistakenly chosing to do a bitmap index scan rather than a
normal index scan.
> Also, are you looking at EXPLAIN or EXPLAIN ANALYZE?
Explain analyze and normal query execution times of several millions of
queries executed on both versions of postgresql, so we can say something
about them statistically.
Best regards,
Arjen
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: 8.2rc1 (much) slower than 8.2dev?
@ 2006-12-07 15:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: Arjen van der Meijden <acmmailing@tweakers.net>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2006-12-07 15:38 UTC (permalink / raw)
To: Arjen van der Meijden <acmmailing@tweakers.net>; +Cc: Jim C. Nasby <jim@nasby.net>; pgsql-performance
Arjen van der Meijden <acmmailing@tweakers.net> writes:
> I've been mailing off-list with Tom and we found at least one
> query that in some circumstances takes a lot more time than it should,
> due to it mistakenly chosing to do a bitmap index scan rather than a
> normal index scan.
Just to clue folks in: the problem queries seem to be cases like
WHERE col1 = 'const'
AND col2 = othertab.colx
AND col3 IN (several hundred integers)
where the table has an index on (col1,col2,col3). 8.2 is generating
a plan involving a nestloop with inner bitmap indexscan on this index,
and using all three of these WHERE clauses with the index. The ability
to use an IN clause (ie, ScalarArrayOpExpr) in an index condition is
new in 8.2, and we seem to have a few bugs left in the cost estimation
for it. The problem is that a ScalarArrayOpExpr effectively causes a
BitmapOr across N index scans using each of the array elements as an
individual scan qualifier. So the above amounts to several hundred
index probes for each outer row. In Arjen's scenario it seems that
the first two index columns are already pretty selective, and it comes
out a lot faster if you just do one indexscan using the first two
columns and then apply the IN-condition as a filter to the relatively
small number of rows you get that way.
What's not clear to me yet is why the 8.2dev code didn't fall into this
same trap, because the ScalarArrayOpExpr indexing code was already there
on 3-June. But we didn't and still don't have any code that considers
the possibility that a potential indexqual condition should be
deliberately *not* used with the index.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PERFORM] 8.2rc1 (much) slower than 8.2dev?
@ 2006-12-14 21:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Arjen van der Meijden <acmmailing@tweakers.net>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2006-12-14 21:10 UTC (permalink / raw)
To: Arjen van der Meijden <acmmailing@tweakers.net>; +Cc: pgsql-patches@postgreSQL.org
Arjen van der Meijden <acmmailing@tweakers.net> writes:
>> The file is attached. (bz)Grepping for 'Was executed on 8.2 final slower
>> than 8.2 dev' with -A1 will show you the timecomparisons, which you
>> could than look up using your favorite editor.
I dug through this file, and it seems that all the cases where 8.2 final
is choosing a really markedly worse plan are instances of the same
query, for which 8.2 chose a nestloop plan with an inner indexscan on
a clustered index. 8.2 final is failing to choose that because in the
nestloop case it's not giving any cost credit for the index's
correlation. Obviously a clustered index should have very high
correlation. In the test case, half a dozen or so heap tuples need to
be fetched per index scan, and because of the correlation it's likely
they are all on the same heap page ... but the costing is assuming that
they are scattered randomly. I had punted on this point back in June
because it seemed too complicated to handle in combination with the
cross-scan caching, but obviously we need to do something. After
thinking a bit more, I propose the attached patch --- to be applied on
top of the other ones I sent you --- which seems to fix the problem
here. Please give it a try.
regards, tom lane
inline patch 1: src/backend/optimizer/path/costsize.c (+32/-13 lines) download inline-1.patch
*** src/backend/optimizer/path/costsize.c.orig Fri Dec 8 15:58:34 2006
--- src/backend/optimizer/path/costsize.c Thu Dec 14 15:37:40 2006
***************
*** 276,288 ****
if (outer_rel != NULL && outer_rel->rows > 1)
{
/*
! * For repeated indexscans, scale up the number of tuples fetched in
* the Mackert and Lohman formula by the number of scans, so that we
! * estimate the number of pages fetched by all the scans. Then
* pro-rate the costs for one scan. In this case we assume all the
! * fetches are random accesses. XXX it'd be good to include
! * correlation in this model, but it's not clear how to do that
! * without double-counting cache effects.
*/
double num_scans = outer_rel->rows;
--- 276,287 ----
if (outer_rel != NULL && outer_rel->rows > 1)
{
/*
! * For repeated indexscans, the appropriate estimate for the
! * uncorrelated case is to scale up the number of tuples fetched in
* the Mackert and Lohman formula by the number of scans, so that we
! * estimate the number of pages fetched by all the scans; then
* pro-rate the costs for one scan. In this case we assume all the
! * fetches are random accesses.
*/
double num_scans = outer_rel->rows;
***************
*** 291,297 ****
(double) index->pages,
root);
! run_cost += (pages_fetched * random_page_cost) / num_scans;
}
else
{
--- 290,316 ----
(double) index->pages,
root);
! max_IO_cost = (pages_fetched * random_page_cost) / num_scans;
!
! /*
! * In the perfectly correlated case, the number of pages touched
! * by each scan is selectivity * table_size, and we can use the
! * Mackert and Lohman formula at the page level to estimate how
! * much work is saved by caching across scans. We still assume
! * all the fetches are random, though, which is an overestimate
! * that's hard to correct for without double-counting the cache
! * effects. (But in most cases where such a plan is actually
! * interesting, only one page would get fetched per scan anyway,
! * so it shouldn't matter much.)
! */
! pages_fetched = ceil(indexSelectivity * (double) baserel->pages);
!
! pages_fetched = index_pages_fetched(pages_fetched * num_scans,
! baserel->pages,
! (double) index->pages,
! root);
!
! min_IO_cost = (pages_fetched * random_page_cost) / num_scans;
}
else
{
***************
*** 312,326 ****
min_IO_cost = random_page_cost;
if (pages_fetched > 1)
min_IO_cost += (pages_fetched - 1) * seq_page_cost;
! /*
! * Now interpolate based on estimated index order correlation to get
! * total disk I/O cost for main table accesses.
! */
! csquared = indexCorrelation * indexCorrelation;
! run_cost += max_IO_cost + csquared * (min_IO_cost - max_IO_cost);
! }
/*
* Estimate CPU costs per tuple.
--- 331,345 ----
min_IO_cost = random_page_cost;
if (pages_fetched > 1)
min_IO_cost += (pages_fetched - 1) * seq_page_cost;
+ }
! /*
! * Now interpolate based on estimated index order correlation to get
! * total disk I/O cost for main table accesses.
! */
! csquared = indexCorrelation * indexCorrelation;
! run_cost += max_IO_cost + csquared * (min_IO_cost - max_IO_cost);
/*
* Estimate CPU costs per tuple.
end of thread, other threads:[~2006-12-14 21:10 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-04 07:44 8.2rc1 (much) slower than 8.2dev? Arjen van der Meijden <acmmailing@tweakers.net>
2006-12-04 14:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-12-04 16:41 ` Arjen van der Meijden <acmmailing@tweakers.net>
2006-12-07 06:01 ` Jim C. Nasby <jim@nasby.net>
2006-12-07 09:13 ` Arjen van der Meijden <acmmailing@tweakers.net>
2006-12-07 15:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-12-14 21:10 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox