8.2rc1 (much) slower than 8.2dev?

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

Arjen van der Meijden <acmmailing(at)tweakers(dot)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

Tom Lane wrote:
> Arjen van der Meijden <acmmailing(at)tweakers(dot)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

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(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

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

Arjen van der Meijden <acmmailing(at)tweakers(dot)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

Arjen van der Meijden <acmmailing(at)tweakers(dot)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