SeqScan vs. IndexScan

From: Vitaliy Garnashevich <vgarnashevich(at)gmail(dot)com>
To: "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org>
Subject: SeqScan vs. IndexScan
Date: 2018-04-18 22:14:48
Message-ID: 439201cf-0a22-8b48-34bd-f311108acd0f@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2018-04-18 22:33:18 Re: SeqScan vs. IndexScan
Previous Message Adrian Klaver 2018-04-18 14:02:32 Re: pg_upgrade help