The following bug has been logged on the website:
Bug reference: 13824
Logged by: Grzegorz G
Email address: grzegorz@thulium.pl
PostgreSQL version: 9.4.5
Operating system: Debian
Description:
Seems related to
(9.4.4,9.3.9) Improve planner's cost estimates for semi-joins and anti-joins
with inner indexscans (Tom Lane, Tomas Vondra)
I have 2 tables: master(t_outbound) and detail (t_outbound_number). I want
to select only those records from t_outbound that have detailed records in
t_outbound number in certain statuses (id_status).
There is an index on t_outbound_number:
"t_on_id_status_1_or_9" btree (id_outbound) WHERE id_status = 1 OR
id_status = 9
Whenever I perform select like below, planner thinks it's going to look up
many rows and falls back to seq scan. If I disable seq scan, it correctly
uses the index.
When the t_outbound_number table grows and percentage of records with status
1 or 9 decreases, index is used.
Seems like planner thinks it's going to retrieve all of the records to
verify EXISTS clause, not just one.
psql=> EXPLAIN ANALYZE SELECT id_outbound,
(EXISTS (SELECT 1 FROM t_outbound_number ton WHERE (id_status = 1 OR
id_status = 9) AND ton.id_outbound=tou.id_outbound))
from t_outbound tou
ORDER BY id ASC;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=2.58..2.59 rows=5 width=10) (actual time=94.805..94.806 rows=5
loops=1)
Sort Key: tou.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on t_outbound tou (cost=0.00..2.52 rows=5 width=10) (actual
time=62.955..94.763 rows=5 loops=1)
SubPlan 1
-> Seq Scan on t_outbound_number ton (cost=0.00..11910.24
rows=40453 width=0) (actual time=18.942..18.942 rows=1 loops=5)
Filter: ((id_outbound = tou.id_outbound) AND ((id_status =
1) OR (id_status = 9)))
Rows Removed by Filter: 114596
Planning time: 0.775 ms
Execution time: 94.853 ms
psql=> set enable_seqscan =off;
SET
psql=> EXPLAIN ANALYZE SELECT id_outbound,
(EXISTS (SELECT 1 FROM t_outbound_number ton WHERE (id_status = 1 OR
id_status = 9) AND ton.id_outbound=tou.id_outbound))
from t_outbound tou
ORDER BY id ASC;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using t_outbound_id_key on t_outbound tou (cost=0.13..14.69
rows=5 width=10) (actual time=0.033..0.103 rows=5 loops=1)
SubPlan 1
-> Index Scan using t_on_id_status_1_or_9 on t_outbound_number ton
(cost=0.42..3082.02 rows=40453 width=0) (actual time=0.015..0.015 rows=1
loops=5)
Index Cond: (id_outbound = tou.id_outbound)
Planning time: 0.811 ms
Execution time: 0.142 ms
