The following bug has been logged on the website:
Bug reference: 8771
Logged by: Martin Junek
Email address: martin.junek@tracmap.co.nz
PostgreSQL version: 9.1.11
Operating system: Ubuntu 12.04
Description:
Hi there,
after upgrading from 9.1.9 to 9.1.11 one of our queries started to run
terribly slow (went from few miliseconds to hours). The problem is better
explained in the following SQL snippet (which is a very simplified version
of the problem). If you run it on 9.1.9, all the SELECTs evaluate in few
miliseconds, if you run it on 9.1.11, it will take probably hours (I didn't
have the patience to wait for it).
Because it works fine in the older version, and also because there's nothing
conceptually wrong with the query, I believe it's a bug that should be
reported and fixed.
Use the following SQL script to reproduce the problem.
-- prepare underlying table with 1m records
create table t1(id int primary key, val double precision);
insert into t1 select generate_series(1, 1000000), random() * 10000000;
-- a function that just takes long (will be used in the sub-query)
create or replace function takes_long(double precision) returns int as $$
select max(id) from t1 where val = $1; -- just to force a slow seq_scan
$$ language sql;
-- create view, that calls the slow function for each row
create view v as
select
id, val, (select takes_long(val)) as whatever
from t1;
-- this works fine (calls the slow function only once, as expected)
select * from v
where id in (50000)
-- in 9.1.9 it will limit the one row and evaluates the slow function only
once (=expected)
-- in 9.1.11 this will run for hours - for some reason evaluates the slow
function for each row (million times)
select * from v
where id in (select 50000)
-- also this will run for hours in 9.1.11 (even though it doesn't even have
to call the function at all - it's not in the select list)
select id from v
where id in (select 50000)
