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
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 |