index not used in joins - Mailing list 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);