Marc Millas <marc.millas@xxxxxxxxxx> 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
