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

Поиск
Список
Период
Сортировка
От dmitry potapov
Тема "WHERE 1 = 2 OR ..." makes planner choose a very inefficient plan
Дата
Msg-id CAJPZb_vmf3dkCv0Rre+VOm6bkJc3hugjEE2h0bgY1WqduDLUbg@mail.gmail.com
обсуждение исходный текст
Ответы Re: "WHERE 1 = 2 OR ..." makes planner choose a very inefficient plan
Re: "WHERE 1 = 2 OR ..." makes planner choose a very inefficient plan
Список 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

--



В списке pgsql-performance по дате отправления:

Предыдущее
От: Franck Routier
Дата:
Сообщение: Re: Postgresql performance degrading... how to diagnose the root cause
Следующее
От: Richard Huxton
Дата:
Сообщение: Re: "WHERE 1 = 2 OR ..." makes planner choose a very inefficient plan