Message-ID: <436B4836.7030300@mall.cz>
Date: Fri, 04 Nov 2005 12:38:30 +0100
From: Michal Taborsky <michal.taborsky@mall.cz>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Searching union views not using indices
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 8bit

Hello everyone.

We are facing a performance problem with views consisting of several 
unioned tables. The simplified schema is as follows:

CREATE TABLE foo (
	foo_object_id	bigint,
	link_id		bigint,
	somedata	text,
	PRIMARY KEY (foo_object_id) );

CREATE TABLE bar (
	bar_object_id	bigint,
	link_id		bigint,
	otherdata	real,
	PRIMARY KEY (bar_object_id) );

There are actually five of such tables, all having two common attributes 
*_object_id and link_id. All tables have indices on link_id, which is 
very selective, close to unique. The *_object_id is unique within this 
scope across all tables, but that's not important.

Then we have a view:

CREATE VIEW commonview AS
SELECT foo_object_id as object_id, link_id, 'It is in foo' as loc
FROM foo

UNION

SELECT bar_object_id as object_id, link_id, 'It is in bar' as loc
FROM bar

We commonly do this:

SELECT object_id FROM commonview WHERE link_id=1234567

The result is sequential scan on all tables, append, sort and then 
filter scan on this whole thing. Which of course is slow as hell. We use 
version 8.0.2.

And now the question: Is there a way to force the planner to push the 
condition lower, so it will use the index? Or do you use some tricks in 
this scenario? Thanks for your suggestions.

Bye.

-- 
Michal Táborský
CTO, Internet Mall, a.s.

Internet Mall - obchody, které si oblíbíte
<http://www.MALL.cz>