pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSeqScan vs. IndexScan
2+ messages / 2 participants
[nested] [flat]
* SeqScan vs. IndexScan
@ 2018-04-18 22:14 Vitaliy Garnashevich <vgarnashevich@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Vitaliy Garnashevich @ 2018-04-18 22:14 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi,
I'm running the same query with "set enable_seqscan = on;" and "set
enable_seqscan = off;":
-> Nested Loop Left Join (cost=0.00..89642.86 rows=1 width=30) (actual
time=1.612..6924.232 rows=3289 loops=1)
Join Filter: (sys_user.user_id = j_6634.id)
Rows Removed by Join Filter: 14330174
-> Seq Scan on sys_user (cost=0.00..89449.85 rows=1 width=16)
(actual time=0.117..39.802 rows=3289 loops=1)
Filter: ...
-> Seq Scan on cmn_user j_6634 (cost=0.00..138.56 rows=4356
width=22) (actual time=0.001..0.973 rows=4358 loops=3289)
(Full plan: https://explain.depesz.com/s/plAO)
-> Nested Loop Left Join (cost=0.56..89643.52 rows=1 width=30) (actual
time=0.589..39.674 rows=3288 loops=1)
-> Index Scan using sys_user_pkey on sys_user
(cost=0.28..89635.21 rows=1 width=16) (actual time=0.542..29.435
rows=3288 loops=1)
Filter: ...
-> Index Scan using cmn_user_pkey on cmn_user j_6634
(cost=0.28..8.30 rows=1 width=22) (actual time=0.002..0.002 rows=1
loops=3288)
Index Cond: (sys_user.user_id = id)
(Full plan: https://explain.depesz.com/s/4QXy)
Why optimizer is choosing SeqScan (on cmn_user) in the first query,
instead of an IndexScan, despite of SeqScan being more costly?
Regards,
Vitaliy
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: SeqScan vs. IndexScan
@ 2018-04-18 22:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: Vitaliy Garnashevich <vgarnashevich@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2018-04-18 22:33 UTC (permalink / raw)
To: Vitaliy Garnashevich <vgarnashevich@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2018-04-18 22:33 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-04-18 22:14 SeqScan vs. IndexScan Vitaliy Garnashevich <vgarnashevich@gmail.com>
2018-04-18 22:33 ` Tom Lane <tgl@sss.pgh.pa.us>
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