Searching union views not using indices - Mailing list pgsql-performance

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>