"Vyacheslav Kalinin" <vka@xxxxxxxx> writes: > QUERY PLAN > Bitmap Heap Scan on t1 (cost= 151.74..5307.59 rows=5000 width=8) > Recheck Cond: ((val > $1) AND (val < $2)) > -> Bitmap Index Scan on idx_t1 (cost=0.00..150.49 rows=5000 width=0) > Index Cond: ((val > $1) AND (val < $2)) > Hmm, why does it expect 5000 rows here? What influences this expectation? It can see that it's got a range constraint on 'val', but not exactly how wide the range is, so the selectivity estimate is DEFAULT_RANGE_INEQ_SEL which is hardwired at 0.005. 0.005 * 1000000 = 5000. > prepare stmt (int) as > select * from t1 where val = $1 or $1 is null; > explain execute stmt(20000); > QUERY PLAN > Seq Scan on t1 (cost=0.00..17401.94 rows=5013 width=8) > Filter: ((val = $1) OR ($1 IS NULL)) > That's the weirdest behavior: where did 5013 rows assumption came from? Why > use seq scan then? I should mention that planner refuses to use anything but > seq scan here even if I explicitly disable it with "set enable_seqscan to > off". It hasn't got a lot of choice: if $1 is null it will have to return the whole table, a case for which an indexscan is unsuitable. I think this falls in the category of "bad query design" not "bad planning". If that really is the behavior you want, and not a typo, consider something like prepare stmt (int) as select * from t1 where val = $1 union all select * from t1 where $1 is null; As for the rowcount estimate, I think it's using DEFAULT_UNK_SEL (which also happens to be 0.005) as the selectivity of an IS NULL test with a non-Var target. That's a bit useless in this case, since for any one call of the query it's either going to be constant true or constant false, but there's not any obvious better way to do it. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
