From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michal Taborsky <michal.taborsky@mall.cz>
Cc: pgsql-performance@postgresql.org
Subject: Re: Searching union views not using indices
Date: Fri, 04 Nov 2005 10:31:34 -0500
Message-ID: <13815.1131118294@sss.pgh.pa.us> (raw)
In-Reply-To: <436B4836.7030300@mall.cz>
References: <436B4836.7030300@mall.cz>
Michal Taborsky <michal.taborsky@mall.cz> writes:
> We are facing a performance problem with views consisting of several
> unioned tables. The simplified schema is as follows:
Perhaps you should show us the real schema, because I cannot duplicate
your complaint on the toy case you show.
regression=# explain SELECT object_id FROM commonview WHERE link_id=1234567;
QUERY PLAN
------------------------------------------------------------------------------------------------
Subquery Scan commonview (cost=41.40..41.66 rows=13 width=8)
-> Unique (cost=41.40..41.53 rows=13 width=16)
-> Sort (cost=41.40..41.43 rows=13 width=16)
Sort Key: object_id, link_id, loc
-> Append (cost=0.00..41.16 rows=13 width=16)
-> Subquery Scan "*SELECT* 1" (cost=0.00..17.12 rows=5 width=16)
-> Index Scan using fooi on foo (cost=0.00..17.07 rows=5 width=16)
Index Cond: (link_id = 1234567)
-> Subquery Scan "*SELECT* 2" (cost=0.00..24.04 rows=8 width=16)
-> Index Scan using bari on bar (cost=0.00..23.96 rows=8 width=16)
Index Cond: (link_id = 1234567)
(11 rows)
(I had to add indexes on link_id to the example, of course.)
As noted by others, you probably want to be using UNION ALL not UNION,
but that's not the crux of the issue.
regards, tom lane
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: tgl@sss.pgh.pa.us, michal.taborsky@mall.cz
Subject: Re: Searching union views not using indices
In-Reply-To: <13815.1131118294@sss.pgh.pa.us>
* 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