pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed"WHERE 1 = 2 OR ..." makes planner choose a very inefficient plan
5+ messages / 5 participants
[nested] [flat]
* "WHERE 1 = 2 OR ..." makes planner choose a very inefficient plan
@ 2013-04-18 14:20 dmitry potapov <potapov.dmitry@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: dmitry potapov @ 2013-04-18 14:20 UTC (permalink / raw)
To: pgsql-performance
Hello,
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%'
)
);
EXPLAIN (ANALYZE, BUFFERS) for this query:
http://explain.depesz.com/s/tcn
Execution time: 2037872.420 ms (almost 34 minutes!!)
If I comment out "1=2 OR", then the plan changes dramatically:
http://explain.depesz.com/s/5rsW
Execution time: 617.778 ms
I know LEFT JOIN or EXISTS instead of NOT IN in this case will give better
plans. What bothers me is not performance of this particular query, but the
strange behavior of query planner. Is this behavior considered normal, or
should I file a bug?
database schema: http://pgsql.privatepaste.com/b297e685c5
version: PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit
package: postgresql91-server.x86_64 9.1.9-1PGDG.rhel6
os: Scientific Linux 6.3
postgresql.conf: http://pgsql.privatepaste.com/e3e75bb789
--
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: "WHERE 1 = 2 OR ..." makes planner choose a very inefficient plan
@ 2013-04-18 14:43 Richard Huxton <dev@archonet.com>
parent: dmitry potapov <potapov.dmitry@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2013-04-18 14:43 UTC (permalink / raw)
To: dmitry potapov <potapov.dmitry@gmail.com>; +Cc: pgsql-performance
On 18/04/13 15:20, dmitry potapov wrote:
> Hello,
>
> 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:
> If I comment out "1=2 OR", then the plan changes dramatically:
What happens if you substitute:
1. 1=3 OR
2. false OR
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: "WHERE 1 = 2 OR ..." makes planner choose a very inefficient plan
@ 2013-04-18 14:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: dmitry potapov <potapov.dmitry@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2013-04-18 14:46 UTC (permalink / raw)
To: dmitry potapov <potapov.dmitry@gmail.com>; +Cc: pgsql-performance
dmitry potapov <potapov.dmitry@gmail.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.)
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: "WHERE 1 = 2 OR ..." makes planner choose a very inefficient plan
@ 2013-05-02 12:48 Simon Riggs <simon@2ndQuadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Simon Riggs @ 2013-05-02 12:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: dmitry potapov <potapov.dmitry@gmail.com>; pgsql-performance
On 18 April 2013 15:46, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> dmitry potapov <potapov.dmitry@gmail.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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: "WHERE 1 = 2 OR ..." makes planner choose a very inefficient plan
@ 2013-05-02 15:00 Claudio Freire <klaussfreire@gmail.com>
parent: Simon Riggs <simon@2ndQuadrant.com>
0 siblings, 0 replies; 5+ messages in thread
From: Claudio Freire @ 2013-05-02 15:00 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; dmitry potapov <potapov.dmitry@gmail.com>; pgsql-performance
On Thu, May 2, 2013 at 9:48 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
>>> 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%'
>>> )
>>> );
>>
...
>
> 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.
How could that be the case without becoming much much worse than large runtimes?
I don't think it's the place of the database to worry about SQL injection.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2013-05-02 15:00 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-04-18 14:20 "WHERE 1 = 2 OR ..." makes planner choose a very inefficient plan dmitry potapov <potapov.dmitry@gmail.com>
2013-04-18 14:43 ` Richard Huxton <dev@archonet.com>
2013-04-18 14:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-05-02 12:48 ` Simon Riggs <simon@2ndQuadrant.com>
2013-05-02 15:00 ` Claudio Freire <klaussfreire@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox