Re: suggestions on improving a query

On Wed, Feb 14, 2007 at 08:22:42AM -0500, Rajarshi Guha wrote:
> (One question not directly related to the problem: when looking at the
> output of explain analyze, I know that one is supposed to start at the
> bottom and move up. Does that that the index scan on pubchem_compound is
> being performed first? Or should I start from the innermost line?)

There's no concept of nodes being executed before others. Each node is
executed as needed. If the case of a nested loop like you have, it
reads one tuple from the outer node (the first child) and then as many
tuples from the inner node as necessary (an index scan may not return
very many). In your case the outer node is another nested loop which
will in turn scan its inner and outer nodes as necessary.

The Limit up the top means that no more than that many tuples will be
requested from child node, so child nodes may be executed once, many
times or not at all.

There are some more comprehensive writeups around, but hopefully this
gives you an idea.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.