Index not used on join with inherited tables
От | Sebastian Böck |
---|---|
Тема | Index not used on join with inherited tables |
Дата | |
Msg-id | 429B3734.2020609@freenet.de обсуждение исходный текст |
Ответы |
Re: Index not used on join with inherited tables
|
Список | pgsql-performance |
Hi all, I'm having another problem with a query that takes to long, because the appropriate index is not used. I found some solutions to this problem, but I think Postgres should do an index scan in all cases. To show the problem I've attached a small script with a testcase. Thanks in advance Sebastian /* tables */ CREATE TABLE test ( id SERIAL PRIMARY KEY, test TEXT ); CREATE TABLE test1 ( id INTEGER PRIMARY KEY ) INHERITS (test); CREATE TABLE test2 ( id INTEGER PRIMARY KEY ) INHERITS (test); CREATE TABLE test3 ( id INTEGER PRIMARY KEY ) INHERITS (test); CREATE TABLE bug ( id INTEGER PRIMARY KEY ); /* views */ CREATE OR REPLACE VIEW working_fast AS SELECT * FROM test WHERE id = 1; CREATE OR REPLACE VIEW working_fast_2 AS SELECT test1.* FROM test1 JOIN bug AS bug1 ON TRUE WHERE test1.id = bug1.id UNION ALL SELECT test2.* FROM test2 JOIN bug AS bug2 ON TRUE WHERE test2.id = bug2.id UNION ALL SELECT test3.* FROM test3 JOIN bug AS bug3 ON TRUE WHERE test3.id = bug3.id; CREATE OR REPLACE VIEW working_slow AS SELECT test.* FROM test JOIN bug ON TRUE WHERE test.id = bug.id; /* data */ CREATE OR REPLACE FUNCTION data () RETURNS BOOLEAN AS $$ BEGIN FOR i IN 1..10000 LOOP INSERT INTO test1 (id,test) VALUES (DEFAULT,i); INSERT INTO test2 (id,test) VALUES (DEFAULT,i); INSERT INTO test3 (id,test) VALUES (DEFAULT,i); END LOOP; RETURN TRUE; END; $$ LANGUAGE plpgsql; SELECT data(); INSERT INTO bug VALUES ('1'); ANALYZE; EXPLAIN ANALYZE SELECT * from working_fast; EXPLAIN ANALYZE SELECT * from working_fast_2; EXPLAIN ANALYZE SELECT * from working_slow;
В списке pgsql-performance по дате отправления: