Ioannis Theoharis wrote:
> let me, i have turned enable_seqscan to off, in order to discourage
> optimizer to choose seq_scan whenever an idex_scan can be used.
>
> But in this case, why optimizer don't chooses seq_scan (discourage is
> different than prevent) ?
You probably know that PostgreSQL uses a cost-based optimizer. The optimizer
chooses different plans based on the cost it calculates for them.
enable_seqscan = OFF is very primitive but effective: it tells the optimizer
to raise the cost of a sequential scan to a value going towards infinity.
When it comes to the choice between seq scan and index scan, the optimizer
will now always choose the index scan. It does not "known" anymore if
sequential scan would be cheaper -- *you* have told the optimizer that it is
not.
Only when there is no other way except seq scan to execute your query at
all, then the optimizer must choose this very costly path. An example is an
unqualified SELECT * FROM table; -- there is no path with an index here.
I hope that answers your first question. As you see, enable_seqscan = OFF
should not be used for production systems, but only for debugging. Perhaps
it's useful to set at query level, but not in postgresql.conf.
Best Regards,
Michael Paesold