From: Josh Berkus <josh@agliodbs.com>
To: Phil Endecott <spam_from_postgresql_lists@chezphil.org>
To: pgsql-performance@postgresql.org
Subject: Re: Optimisation of INTERSECT expressions
Date: Tue, 23 Mar 2004 09:17:40 -0800
Message-ID: <200403230917.40133.josh@agliodbs.com> (raw)
In-Reply-To: <20040323162823.B4131D1EC40@svr1.postgresql.org>
References: <20040323162823.B4131D1EC40@svr1.postgresql.org>
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
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: josh@agliodbs.com, spam_from_postgresql_lists@chezphil.org
Subject: Re: Optimisation of INTERSECT expressions
In-Reply-To: <200403230917.40133.josh@agliodbs.com>
* 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