On 18 April 2013 15:46, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > dmitry potapov <potapov.dmitry@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
