index not used in joins
От | Sebastian Böck |
---|---|
Тема | index not used in joins |
Дата | |
Msg-id | 41BD55C7.1090502@freenet.de обсуждение исходный текст |
Список | pgsql-general |
Hello all, i have a problem with index usage and joins. Attached is some SQL demonstrating my problem; Why is the index only used in the 2nd query? Can anybody explain me how to avoid/fix this. Thanks in advance Sebastian CREATE TABLE users ( login NAME NOT NULL PRIMARY KEY, datum TIMESTAMP, version INTEGER ); CREATE TABLE test ( datum TIMESTAMP NOT NULL, version INTEGER NOT NULL, approved TIMESTAMP ); CREATE OR REPLACE VIEW v AS SELECT t.* FROM test AS t INNER JOIN users AS u ON t.datum <= u.datum AND (t.version = u.version OR t.approved IS NOT NULL); CREATE OR REPLACE FUNCTION fill () RETURNS BOOLEAN AS ' DECLARE i INTEGER; BEGIN FOR i IN 1..1000 LOOP EXECUTE ''INSERT INTO test (datum,version) VALUES (now(),''|| i || '')''; END LOOP; RETURN TRUE; END; ' LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION approved () RETURNS BOOLEAN AS ' DECLARE i INTEGER; BEGIN FOR i IN 1..1000 LOOP EXECUTE ''INSERT INTO test (datum,version,approved) VALUES (now(),''|| i || '',now())''; END LOOP; RETURN TRUE; END; ' LANGUAGE plpgsql; SELECT fill(); SELECT fill(); SELECT fill(); SELECT fill(); SELECT fill(); SELECT fill(); SELECT fill(); SELECT fill(); SELECT fill(); SELECT fill(); SELECT fill(); SELECT fill(); SELECT fill(); SELECT fill(); SELECT fill(); SELECT fill(); SELECT fill(); SELECT fill(); SELECT fill(); SELECT fill(); SELECT approved(); INSERT INTO users (login,datum,version) VALUES ('sb',now(),'999'); CREATE INDEX test_ ON test (datum); CREATE INDEX test_999 ON test (datum) WHERE version = '999' OR approved IS NOT NULL; ANALYZE; EXPLAIN ANALYZE SELECT * FROM v; EXPLAIN ANALYZE SELECT t.* FROM test AS t INNER JOIN users AS u ON t.datum <= u.datum AND (t.version = '999' OR t.approvedIS NOT NULL);
В списке pgsql-general по дате отправления: