Re: "WHERE 1 = 2 OR ..." makes planner choose a very inefficient plan

On 18 April 2013 15:46, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> dmitry potapov <potapov(dot)dmitry(at)gmail(dot)com> writes:
>> I recently stumbled upon on what could be a planner bug or a corner case.
>> If "<false condition> OR ..." is added to WHERE clause of SELECT query,
>> then the planner chooses a very inefficient plan. Consider a query:
>
>> SELECT count(k0.id)
>> FROM k0
>> WHERE 1 = 2
>> OR k0.id IN (
>> SELECT k1.k0_id
>> FROM k1
>> WHERE k1.k1k2_id IN (
>> SELECT k2.k1k2_id
>> FROM k2
>> WHERE k2.t = 2
>> AND (coalesce(k2.z, '')) LIKE '%12%'
>> )
>> );
>
> Perhaps you should fix your application to not generate such incredibly
> silly SQL. Figuring out that 1=2 is constant false and throwing it away
> costs the server easily a thousand times as many instructions as it
> would take for the client to not emit that in the first place.
>
> The reason you don't get a nice semijoin plan when you do that is that
> conversion of IN clauses to semijoins happens before
> constant-subexpression simplification. So the planner hasn't yet
> figured out that the OR is useless when it would need to know that to
> produce a good plan. (And no, we can't just flip the order of those two
> steps. Doing two rounds of const-simplification wouldn't be a good
> answer either, because it would penalize well-written queries to benefit
> badly-written ones.)

The situation shown could be the result of SQL injection attack.

It would be nice to have a switch to do additional checks on SQL
queries to ensure such injections don't cause long runtimes to return
useless answers.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services