X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id DB679D1EBBE for ; Tue, 23 Mar 2004 17:18:20 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 77854-08 for ; Tue, 23 Mar 2004 13:18:17 -0400 (AST) Received: from davinci.ethosmedia.com (server228.ethosmedia.com [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 9E399D1DBAF for ; Tue, 23 Mar 2004 13:18:16 -0400 (AST) Received: from [63.195.55.98] (HELO 192.168.1.29) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 4677831; Tue, 23 Mar 2004 09:19:33 -0800 From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: "Phil Endecott" , pgsql-performance@postgresql.org Subject: Re: Optimisation of INTERSECT expressions Date: Tue, 23 Mar 2004 09:17:40 -0800 User-Agent: KMail/1.5.4 References: <20040323162823.B4131D1EC40@svr1.postgresql.org> In-Reply-To: <20040323162823.B4131D1EC40@svr1.postgresql.org> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200403230917.40133.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/313 X-Sequence-Number: 6169 Phil, > So I suppose I'll have to find a more sophisticated way to generate my > queries. Imagine a user interface for a search facility with various > buttons and text entry fields. At the moment, for each part of the search > that the user has enabled I create a string of SQL. I then compose them > into a single statement using INTERSECT. Each sub-query always returns the > same attribute, but to make things complicated they may come from different > tables. It now seems that I'll have to merge the queries more thoroughly. > Does anyone have any suggestions about how to do this? I'd like a nice > general technique that works for all possible subqueries, as my current > composition with INTERSECT does. I've done this but it involves a choice between a lot of infrastrucure for fully configurable queries, or limiting user choice. The former option requires that you construct reference tables holding what search fields are available, what kind of values they hold, and what operators to use while querying, as well as a table storing the joins used for the various tables that can be queried. Based on that, you can construct dynamically a query on any field or combo of fields listed in your reference tables. If search options are more constrained, you can simply take the easier path of hard-coding the query building blocks into a set-returning function. I do this all the time for Web search interfaces, where the user only has about 9 things to search on. -- -Josh Berkus Aglio Database Solutions San Francisco