Thread: BUG #12805: Planner estimates query at higher cost when execution can be skipped

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.