Marc Millas <marc.millas@mokadb.com> writes:
> I read your answer, Tom, but I cannot connect it to my measurements: why
> adding the index did slow the request twice ??
Are you referring to
>>> if I do create a gist index on geometry column of the big table,
>>> both syntax takes 21 seconds.
? That result is pretty much what I'd expect. If the planner has
to form a nestloop-with-inner-indexscan between a small table and
a big one, it's pretty much always going to prefer to put the small
table on the outside of the loop if it can. The cost of such a
loop is going to be more or less number of outer rows times the
log of the number of inner rows (assuming at great risk of
oversimplification that an index probe into a table of size N
requires about O(log N) work), and it's not hard to see that
S * log(B) is less than B * log(S). You seem to have lucked into a
case where the other way comes out faster despite that, which perhaps
can be explained by buffering effects, but it's not something to bet
on across-the-board.
(If you see this holding consistently, maybe it'd be advisable to
reduce the planner's effective_cache_size parameter to something
closer to shared_buffers, as it seems to indicate that fetches
from kernel space are pretty expensive on your platform.)
regards, tom lane