Re: SeqScan vs. IndexScan - Mailing list pgsql-performance

From Tom Lane
Subject Re: SeqScan vs. IndexScan
Date
Msg-id 24997.1524090798@sss.pgh.pa.us
Whole thread Raw
In response to SeqScan vs. IndexScan  (Vitaliy Garnashevich <vgarnashevich@gmail.com>)
List pgsql-performance
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


pgsql-performance by date:

Previous
From: Vitaliy Garnashevich
Date:
Subject: SeqScan vs. IndexScan
Next
From: Kaushal Shriyan
Date:
Subject: Performance issues while running select sql query