From: Michal Taborsky <michal.taborsky@mall.cz>
To: pgsql-performance@postgresql.org
Subject: Searching union views not using indices
Date: Fri, 04 Nov 2005 12:38:30 +0100
Message-ID: <436B4836.7030300@mall.cz> (raw)
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: michal.taborsky@mall.cz
Subject: Re: Searching union views not using indices
In-Reply-To: <436B4836.7030300@mall.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox