X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6AE85D91AD for ; Fri, 4 Nov 2005 11:31:35 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 40480-05 for ; Fri, 4 Nov 2005 15:31:31 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 9EB43D9135 for ; Fri, 4 Nov 2005 11:31:33 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id jA4FVYjS013816; Fri, 4 Nov 2005 10:31:34 -0500 (EST) To: Michal Taborsky Cc: pgsql-performance@postgresql.org Subject: Re: Searching union views not using indices In-reply-to: <436B4836.7030300@mall.cz> References: <436B4836.7030300@mall.cz> Comments: In-reply-to Michal Taborsky message dated "Fri, 04 Nov 2005 12:38:30 +0100" Date: Fri, 04 Nov 2005 10:31:34 -0500 Message-ID: <13815.1131118294@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.006 required=5 tests=[AWL=0.006] X-Spam-Score: 0.006 X-Spam-Level: X-Archive-Number: 200511/78 X-Sequence-Number: 15335 Michal Taborsky 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