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.approved IS NOT NULL);
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
