Bug with index-usage?
От | Sebastian Böck |
---|---|
Тема | Bug with index-usage? |
Дата | |
Msg-id | 4378C45C.7090306@freenet.de обсуждение исходный текст |
Ответы |
Re: Bug with index-usage?
Re: Bug with index-usage? Re: Bug with index-usage? Re: Bug with index-usage? Re: Bug with index-usage? |
Список | pgsql-general |
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';
В списке pgsql-general по дате отправления: