From: Tom Lane <tgl@sss.pgh.pa.us>
To: Vitaliy Garnashevich <vgarnashevich@gmail.com>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: SeqScan vs. IndexScan
Date: Wed, 18 Apr 2018 18:33:18 -0400
Message-ID: <24997.1524090798@sss.pgh.pa.us> (raw)
In-Reply-To: <439201cf-0a22-8b48-34bd-f311108acd0f@gmail.com>
References: <439201cf-0a22-8b48-34bd-f311108acd0f@gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, vgarnashevich@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: SeqScan vs. IndexScan
In-Reply-To: <24997.1524090798@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox