Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 762D3475A21 for ; Mon, 15 Jul 2002 16:12:51 -0400 (EDT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 6D6B1D600; Mon, 15 Jul 2002 13:12:52 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 631205C02; Mon, 15 Jul 2002 13:12:52 -0700 (PDT) Date: Mon, 15 Jul 2002 13:12:52 -0700 (PDT) From: Stephan Szabo To: Josh Berkus Cc: Subject: Re: Indexing UNIONs In-Reply-To: <200207151200.07766.josh@agliodbs.com> Message-ID: <20020715130911.V41271-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200207/222 X-Sequence-Number: 8600 On Mon, 15 Jul 2002, Josh Berkus wrote: > Folks, > > I have two tables which are often browsed together through a UNION view, like: > > CREATE VIEW two_tables AS > SELECT t1.id, t1.name, t1.abbreviation, t1.juris_id > FROM t1 > UNION ALL > SELECT t2.id, t2.name, NULL, t2.juris_id > FROM t2; > > This works fine as a view, since I have made the id's unique between the two > tables (using a sequence). However, as t1 has 100,000 records, it is > vitally important that queries against this view use an index. We had a discussion recently on -general about this. Right now the planner won't push the conditions down into the arms of the union because noone's been sure under what conditions the optimization is safe.