Re: Indexes performance

charavay <c(dot)charavay(at)ibcp(dot)fr> writes:
> ... So the planner decides to scan 33 000 000 of tuples and we would like to
> force it to scan the table dic (303 000 tuples) and to use
> the index on the integer index to execute the join.

I'm mystified why you think that that will be a superior plan. It still
requires visiting every row of the larger table (I assume that all of
the larger table's rows do join to some row of the smaller table).
All that it accomplishes is to force those visits to occur in a
quasi-random order; which not only loses any chance of kernel read-ahead
optimizations, but very likely causes each page of the table to be read
more than once.

AFAICT the planner made exactly the right choice by picking a hashjoin.
Have you tried comparing its estimates against actual runtimes for the
different plans? (See EXPLAIN ANALYZE.)

Offhand the only way I can think of to force it to do the nestloop the
other way around from what it wants to is to temporarily drop the
index it wants to use. You can do that conveniently like so:

begin;
alter table dic drop constraint dic_pkey;
explain analyze select ...;
rollback;

which of course would be no good for production, but it should at least
serve to destroy your illusions about wanting to do it in production.

regards, tom lane