pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (8+ messages)  latest in thread

Message-ID: <200403230917.40133.josh@agliodbs.com>
Permalink:  ../200403230917.40133.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200403230917.40133.josh@agliodbs.com

 · 

reply

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