Trigve Siver wrote:
----- Original Message ----
From: Richard Huxton <[EMAIL PROTECTED]>
To: Trigve Siver <[EMAIL PROTECTED]>
Sent: Thursday, May 31, 2007 10:33:40 AM
Subject: Re: [SQL] slow query execution
So - you want something like:
The user runs a query ("all blue things") and that gives a list of
results. They can then filter those results further ("shape=round") and
you want to highlight those elements that match.
You either can't or don't want to filter in the application, rather you
would like to run this as two queries but need to match up results from
the second query with the first query (your list).
Suggestion:
For the first query, make sure you have the relevant primary key columns
in your query and do:
CREATE TEMPORARY TABLE my_results AS SELECT ...
Then, you can join against that table in the second query. The temporary
table will exist until you disconnect - see CREATE TABLE for details.
I think that I can use temporary tables with my previous soultion. As you
mentioned,
I can create temp table with the select ("all blue things")[main select]. (I think I can also add row_numbers
to each record as I want to jump to first record in my list which satisfy ("shape=round")
condition) When ("shape=round") Query will be made and the:
a)I can make join in this the query with my temp table (as you mentioned)
b)I can make this query on temp table. But the temporary table haven't indexes.
You can add indexes if you want them. You'll also want to run analyze
against the temp table.
But when main select is some complicated select then (b) can be used.
thanks
Trigve
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
