Re: Planner features, discussion

On 13/07/10 19:49, pasman pasmański wrote:
> Hello.
>
> I propose 2 features for planner:
>
> 1. Planner will estimate 2 x statistics: time of query with cache empty
> and with cache filled.

How would it know what is in cache and how long it'd take to fetch
things into cache that aren't already there?

Which cache(s)? shared_buffers? The OS cache of file system data? RAID
controller read caches? Disk drive read caches? Caches of a big SAN
iSCSI/FC target?

PostgreSQL relies on the OS and hardware to take care of all that, it
just asks for the data. It has very little idea how much of that data is
how "close" in caching terms, and how long it'll take to retrieve. The
very blunt instrument "effective_cache_size" and the random/sequential
IO cost knobs provide only the vaguest and most unrealistic guidance.

This might sound weird, but you need to realize that the planner needs
to keep things somewhat simple to be fast, and it *can't* try to keep
track of and simulate all levels of the system's caching behaviours.
Especially since some levels of caching may be completely invisible not
only to PostgreSQL, but even to the OS.

Consider a PostgreSQL instance running in a virtual machine, where the
storage is provided by a fibre channel SAN. The guest OS doesn't even
know it's virtualized - and that the host OS is caching its disk in RAM.
The host OS doesn't know that the SAN that's backing the disk has oodles
of RAM in which it's doing its own caching.

How is PostgreSQL supposed to figure out what's in cache?

> 2. Two levels of plannig: standard and long.
> Long planning may be used when standard optimization
> generate slow plan, and may use advanced algebraic transformations:

I do like the idea of being able to tell the planner "put more time into
trying to transform this query in ways that might make it faster".

However, that does mean two modes to be debugged, and a less-used "long"
mode of greater complexity to be maintained. Who's going to do the
coding to build and maintain this? What specific optimizations would you
propose for the "long" mode? I'm not sure I understand the one you did
propose as it applies to SQL ... wouldn't it involve a lot more
knowledge of what the two functions in question do than the planner ever
has?

--
Craig Ringer