Обсуждение: different results based solely on existence of index (no, seriously)
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
);
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
);
reproduced it on:
"PostgreSQL 8.3.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu7)"
3 rows with index, 2 rows without.
can not reproduce it on:
- "PostgreSQL 8.1.10 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.3 20070831 (prerelease) (Ubuntu 4.1.2-16ubuntu1)"
- "PostgreSQL 8.2.6 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)"
- "PostgreSQL 8.2.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu4)"
they allways return 2 rows.
hth
WBL
"PostgreSQL 8.3.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu7)"
3 rows with index, 2 rows without.
can not reproduce it on:
- "PostgreSQL 8.1.10 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.3 20070831 (prerelease) (Ubuntu 4.1.2-16ubuntu1)"
- "PostgreSQL 8.2.6 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)"
- "PostgreSQL 8.2.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu4)"
they allways return 2 rows.
hth
WBL
On Aug 12, 2008, at 3:53 AM, Willy-Bas Loos wrote: > reproduced it on: > "PostgreSQL 8.3.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) > 4.2.3 (Ubuntu 4.2.3-2ubuntu7)" > 3 rows with index, 2 rows without. > > can not reproduce it on: > - "PostgreSQL 8.1.10 on i486-pc-linux-gnu, compiled by GCC cc (GCC) > 4.1.3 20070831 (prerelease) (Ubuntu 4.1.2-16ubuntu1)" > - "PostgreSQL 8.2.6 on i686-pc-mingw32, compiled by GCC gcc.exe > (GCC) 3.4.2 (mingw-special)" > - "PostgreSQL 8.2.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) > 4.2.3 (Ubuntu 4.2.3-2ubuntu4)" > they allways return 2 rows. > > hth > WBL reproduced on: PostgreSQL 8.3.1 on i386-apple-darwin9.4.0, compiled by GCC i686-apple- darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5465) 3rows with index, 2 rows without Ries
On Mon, Aug 11, 2008 at 10:35:26PM -0500, Matthew Dennis wrote: > 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) I have reproduced it on 8.3.3. Just FYI, a bug isn't a bug unless you can reproduce it on the latest minor version, in this case 8.3.3, of the major version, in this case 8.3, that the bug appears in. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Re: different results based solely on existence of index (no, seriously)
От
"shakahshakah@gmail.com"
Дата:
On Aug 12, 8:17 am, p...@rvt.dds.nl (ries van Twisk) wrote: > On Aug 12, 2008, at 3:53 AM, Willy-Bas Loos wrote: > > > > > reproduced it on: > > "PostgreSQL 8.3.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) > > 4.2.3 (Ubuntu 4.2.3-2ubuntu7)" > > 3 rows with index, 2 rows without. > > > can not reproduce it on: > > - "PostgreSQL 8.1.10 on i486-pc-linux-gnu, compiled by GCC cc (GCC) > > 4.1.3 20070831 (prerelease) (Ubuntu 4.1.2-16ubuntu1)" > > - "PostgreSQL 8.2.6 on i686-pc-mingw32, compiled by GCC gcc.exe > > (GCC) 3.4.2 (mingw-special)" > > - "PostgreSQL 8.2.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) > > 4.2.3 (Ubuntu 4.2.3-2ubuntu4)" > > they allways return 2 rows. > > > hth > > WBL > > reproduced on: > PostgreSQL 8.3.1 on i386-apple-darwin9.4.0, compiled by GCC i686-apple- > darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5465) > 3rows with index, 2 rows without > > Ries FWIW, reproduced (3 rows w/index, 2 w/o) on: PostgreSQL 8.3.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 (Ubuntu 4.1.2-0ubuntu4)
"Matthew Dennis" <mdennis@merfer.net> writes: > 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. I don't see any failure in 8.3 branch tip. I think the bug was fixed here: http://archives.postgresql.org/pgsql-committers/2008-06/msg00336.php regards, tom lane
On Aug 12, 2008, at 3:53 AM, Willy-Bas Loos wrote: > reproduced it on: > "PostgreSQL 8.3.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) > 4.2.3 (Ubuntu 4.2.3-2ubuntu7)" > 3 rows with index, 2 rows without. > > can not reproduce it on: > - "PostgreSQL 8.1.10 on i486-pc-linux-gnu, compiled by GCC cc (GCC) > 4.1.3 20070831 (prerelease) (Ubuntu 4.1.2-16ubuntu1)" > - "PostgreSQL 8.2.6 on i686-pc-mingw32, compiled by GCC gcc.exe > (GCC) 3.4.2 (mingw-special)" > - "PostgreSQL 8.2.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) > 4.2.3 (Ubuntu 4.2.3-2ubuntu4)" > they allways return 2 rows. > > hth > WBL reproduced on: PostgreSQL 8.3.1 on i386-apple-darwin9.4.0, compiled by GCC i686-apple- darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5465) 3rows with index, 2 rows without Ries