Vitaliy Garnashevich <vgarnashevich@gmail.com> writes:
> I'm running the same query with "set enable_seqscan = on;" and "set
> enable_seqscan = off;":
> ...
> Why optimizer is choosing SeqScan (on cmn_user) in the first query,
> instead of an IndexScan, despite of SeqScan being more costly?
Because it cares about the total plan cost, not the cost of any one
sub-node. In this case, the total costs at the join level are fuzzily
the same, but the indexscan-based join has worse estimated startup cost,
so it prefers the first choice.
The real problem here is the discrepancy between estimate and reality
for the number of rows out of the sys_user scan; because of that, you're
going to get garbage choices at the join level no matter what :-(.
You should look into what's causing that misestimate and whether you
can reduce the error, perhaps by providing better stats or reformulating
the filter conditions in a way the optimizer understands better.
regards, tom lane