In reference to the script below (I know it can be rewritten, that's not the
point), I get 3 rows if the referenced index exists but only two rows if it
does not. This is observable and repeatable just by dropping/creating the
index. Drop the index and two rows are returned. Create the index, three
rows are returned. Drop the index, two rows again. In addition, in no case
does the selected column t2.c2 actually contain a value (it's always null).
Since in the 3 row case, it returns a row with t1.c1=2, I would have
expected a value from t2 (if you add t2.c1 to select clause you can see that
is null as well).
It's probably worth mentioning (since it actually took me a while to notice)
that the plans are subtlety different. Neither plan (with or without index
existing) actually uses the index, but in one case there is an extra filter
node.
version string is PostgreSQL 8.3.1 on i686-redhat-linux-gnu, compiled by GCC
gcc (GCC) 4.1.2 20070925 (Red Hat 4.1.2-33)
create table t1(c1 int);
create table t2(c1 int, c2 timestamp with time zone);
--problem index
create index someidx on t2 using btree(c2);
insert into t1 values (1),(2),(3);
insert into t2 values(2, now());
select
t1.c1,
t2.c2
from
t1
left join t2 on
t1.c1 = t2.c1
where
t2.c2 is null
or (
t2.c2 = (select max(c2) from t2 where t1.c1 = t2.c1)
and t2.c2 < now() - '1 day'::interval
);
