Someone has reported to me that VACUUM ANALYZE is causing different
results for the same query. They believe it is caused by merge join.
I tested in both 8.1.X and CVS HEAD and both appear to be affected. SQL
test attached.
--
Bruce Momjian http://candle.pha.pa.us
SRA OSS, Inc. http://www.sraoss.com
+ If your life is a hard drive, Christ can be your backup. +
DROP TABLE t1;
DROP TABLE t3;
DROP TABLE t2;
CREATE TABLE t2
(
t2_id int4 NOT NULL,
CONSTRAINT t2_pkey PRIMARY KEY (t2_id)
)
WITHOUT OIDS;
CREATE TABLE t1
(
t1_id int4 NOT NULL,
t2_id int4,
CONSTRAINT t1_pkey PRIMARY KEY (t1_id),
CONSTRAINT fk_t2 FOREIGN KEY (t2_id)
REFERENCES t2 (t2_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;
CREATE TABLE t3
(
t2_id int4 NOT NULL,
t3_id int4 NOT NULL,
CONSTRAINT t3_pkey PRIMARY KEY (t2_id, t3_id),
CONSTRAINT fk_t2 FOREIGN KEY (t2_id)
REFERENCES t2 (t2_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;
INSERT INTO t2 (t2_id) VALUES (21);
INSERT INTO t3 (t2_id, t3_id) VALUES (21, 31);
INSERT INTO t3 (t2_id, t3_id) VALUES (21, 32);
INSERT INTO t1 (t1_id, t2_id) VALUES (2, NULL);
INSERT INTO t1 (t1_id, t2_id) VALUES (1, 21);
set enable_hashjoin to off;
select *
from t1
left outer join t2
on t1.t2_id = t2.t2_id
left outer join t3
on t2.t2_id = t3.t2_id;
VACUUM ANALYZE;
set enable_hashjoin to on;
select *
from t1
left outer join t2
on t1.t2_id = t2.t2_id
left outer join t3
on t2.t2_id = t3.t2_id;
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
