Vitaliy Garnashevich <vgarnashevich@xxxxxxxxx> 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
