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  (Josh Berkus)
Список: 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 по дате сообщения:

От: Bricklen Anderson
Дата:
Сообщение: Re: poor performance involving a small table
От: Yves Vindevogel
Дата:
Сообщение: Drop / create indexes and vacuumdb