Jaime Casanova wrote:
On 11/14/05, Sebastian Böck <sebastianboeck@xxxxxxxxxx> wrote:
Hello,
I get unpredictibale results selecting from a view depending on
index-usage.
Please see the attached script for details.
Is it a bug or some "weird feature"?
Any help appreciated to get predictibale results
Sebastian
CREATE TABLE test1 (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
INSERT INTO test1 (name) VALUES ('test1_1');
INSERT INTO test1 (name) VALUES ('test1_2');
INSERT INTO test1 (name) VALUES ('test1_3');
CREATE TABLE test2 (
id SERIAL PRIMARY KEY,
type TEXT NOT NULL CHECK (type IN ('a','b','c')),
test1_id INTEGER REFERENCES test1
);
INSERT INTO test2 (type,test1_id) VALUES ('a',1);
INSERT INTO test2 (type,test1_id) VALUES ('a',2);
INSERT INTO test2 (type,test1_id) VALUES ('a',3);
INSERT INTO test2 (type,test1_id) VALUES ('b',1);
INSERT INTO test2 (type,test1_id) VALUES ('b',2);
INSERT INTO test2 (type,test1_id) VALUES ('b',3);
INSERT INTO test2 (type,test1_id) VALUES ('c',1);
INSERT INTO test2 (type,test1_id) VALUES ('c',2);
INSERT INTO test2 (type,test1_id) VALUES ('c',3);
CREATE OR REPLACE VIEW test AS
SELECT test2.*
FROM test2
LEFT JOIN test2 AS t2 ON
test2.type IN ('c','b') AND
t2.type = 'a';
SELECT * from test WHERE type = 'a';
CREATE INDEX index_a ON test2 (id) WHERE type = 'a';
CREATE INDEX index_b ON test2 (id) WHERE type = 'b';
CREATE INDEX index_c ON test2 (id) WHERE type = 'c';
SET enable_seqscan TO OFF;
SELECT * from test WHERE type = 'a';
i don't have my machine at hand but i don't think that even the select
is right, you have a join but without joining clauses you will get a
cartesian product...
what do you believe is the right answer... just for my probe later...
I think it should be:
id | type | test1_id
----+------+----------
1 | a | 1
2 | a | 2
3 | a | 3
because a
EXPLAIN SELECT * from test WHERE type = 'a';
shows some weird assumptions
Index Scan using index_a on test2 (cost=0.00..4.69 rows=1 width=40)
Filter: (("type" = 'c'::text) OR ("type" = 'b'::text))
note that index_a is defined as:
CREATE INDEX index_a ON test2 (id) WHERE type = 'a';
Sebastian
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
