Re: Select queries which violates table constrains

Joni Martikainen <joni(at)shade-fx(dot)com> writes:
> I investigated some select query performance issues and noticed that
> postgresql misses some obvious cases while processing SELECT query. I
> mean the case where WHERE clause contains statement which condition
> would be against table structure. (excuse my language, look the code)

Your example does what you want if you set constraint_exclusion to ON:

regression=# explain SELECT somecolumn FROM test WHERE somecolumn IS NULL;
QUERY PLAN
------------------------------------------------------
Seq Scan on test (cost=0.00..25.10 rows=8 width=12)
Filter: (somecolumn IS NULL)
Planning time: 0.055 ms
(3 rows)

regression=# set constraint_exclusion = on;
SET
regression=# explain SELECT somecolumn FROM test WHERE somecolumn IS NULL;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: false
Planning time: 0.065 ms
(3 rows)

There may be other cases where the planner could be smarter, but in this
particular case it intentionally doesn't check for this sort of situation
by default, because (as you say) the case only happens with badly-written
queries, and (as the above output demonstrates) we take rather a big hit
in planning time to make those checks.

regards, tom lane