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>

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Reply via email to