From: Richard Huxton <dev@archonet.com>
To: Trigve Siver <trigves@yahoo.com>
Cc: PostgreSQL SQL List <pgsql-sql@postgresql.org>
Subject: Re: slow query execution
Date: Thu, 31 May 2007 11:22:40 +0100
Message-ID: <465EA1F0.6090001@archonet.com> (raw)
In-Reply-To: <75573.99594.qm@web52704.mail.re2.yahoo.com>
References: <75573.99594.qm@web52704.mail.re2.yahoo.com>
Trigve Siver wrote:
> ----- Original Message ----
> From: Richard Huxton <dev@archonet.com>
> To: Trigve Siver <trigves@yahoo.com>
> 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
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-sql@postgresql.org
Cc: dev@archonet.com, trigves@yahoo.com
Subject: Re: slow query execution
In-Reply-To: <465EA1F0.6090001@archonet.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