Message-ID: <429B3734.2020609@freenet.de>
Date: Mon, 30 May 2005 17:54:28 +0200
From: =?ISO-8859-1?Q?Sebastian_B=F6ck?= <sebastianboeck@freenet.de>
User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.7.8) Gecko/20050512
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Index not used on join with inherited tables
Content-Type: multipart/mixed; boundary="------------080806010405030002040702"

This is a multi-part message in MIME format.
--------------080806010405030002040702
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------080806010405030002040702
Content-Type: text/plain;
 name="bug.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="bug.sql"

/* 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;

--------------080806010405030002040702--