The following bug has been logged on the website:
Bug reference: 12805
Logged by: Aaron Brashears
Email address: abrashears@justin.tv
PostgreSQL version: 9.3.5
Operating system: Linux kernel 3.13.0-73 64 bit
Description:
The issue appears to be when a SQL statement contains a clause which causes
no rows to be returned and the planner detects this condition, the returned
plan is more expensive than a plan that actually has to run and read
things.
To reproduce the issue:
create table trivial_plans (
id integer primary key
);
insert into trivial_plans (id) values (1),(2),(3),(4),(5),(6),(7);
-- simple query
explain select * from trivial_plans where id = 5;
QUERY PLAN
---------------------------------------------------------------------------------------------
Index Only Scan using trivial_plans_pkey on trivial_plans (cost=0.15..6.17
rows=1 width=4)
Index Cond: (id = 5)
-- query which never returns a row because of "0=1" where clause which is
always false.
explain select * from trivial_plans where id = 5 and 0 = 1;
QUERY PLAN
--------------------------------------------------------------------
Result (cost=0.00..34.00 rows=1 width=4)
One-Time Filter: false
-> Seq Scan on trivial_plans (cost=0.00..34.00 rows=1 width=4)
Note that the cost of the query which will never need to execute or read
rows is estimated at a cost of 0.00..34.0 -- higher than a plan that
actually has to do work and estimated with a cost cap of 6.17. I would
expect the cost of the second query cost to be 0.00..0.01 or 0.00..0.0 and
even rows = 0.