Re: Planner really hates nested loops

Tom Lane wrote:

>What exactly did you do to force the various plan choices? (I see some
>ridiculous choices of indexscans, for instance, suggesting improper use
>of enable_seqscan in some cases.)
>
Except for forcing a hash with indexes (to show that increased use of
indexes is not necessarily good), the "ridiculous choices of indexscans"
are straight from the planner, i.e. I did not use enable_seqscan.
Obviously, the alternative join methods were obtained by disabling hash
joins and merge joins.

> And what's the "cache rows" and "disk
>rows" stuff, and how do you know that what you were measuring is
>actually what you think it is? I have zero confidence in
>Windows-atop-ATA as a platform for measuring disk-related behaviors,
>because I don't think you can control or even know what caching is
>going on.
>
>
The terms are just abbreviated headings to make it easier to check what
you're looking at. "Cache" refers to repeated runs without disk I/O.
"Disk" refers to a completely initialized system with no PostgreSQL data
in the OS cache (i.e. after a reboot - this is Benchmarking 101). All
results were verified with *at least* two runs at different times. This
is not to say the "disk" results are an accurate or absolute benchmark,
but they're useful as a reference when looking at the cached results.

In any case, I can get the same "cached" results by increasing buffers
to take up most of the memory and thereby make them the defacto cache.

With respect, could we please focus on the point of this thread? I've
spent a great deal of time experimenting with PostgreSQL over the last
couple of months, including reading every known web page regarding
tuning and following every post in this list in that period. I'm
confident that my results here are what most people will experience when
trying PostgreSQL, and I'd like to help in a constructive way.