Обсуждение: Speed up a query - using a temp table

I have a query of the form:

select * from (
select contact.id
, contact.name
, (select from detail inner join myDetail using (detail.id) ) as cost
from contact
inner join user using (user_id) and user_id = 1
) as OUTTABLE
where cost > 30;


The problem is that all the tables in the query are big, so it takes a
long time to get results from OUTTABLE (around 20 seconds) . When I
add the where clause at the end (cost > 30), the query becomes too
long (around 200 seconds sometimes).

To make the query run faster, I thought of creating a temp table to
store OUTTABLE, and then just filter on the temp table using cost >
30. That would improve the speed of the query tremendously (total time
was around 23 seconds) . The problem with that approach is that I'm
writing an application, and more than one user might need to run the
query at the same time. (Different user should get different results
because they have different user ids, and other numbers in the query
would be different). Also, I can't just create and drop temp tables in
an enterprise application (we are not using ejbs in our applications
either), it seems like bad style.

Anyhow, how could I make this query faster so that the values of
OUTTABLE is retrieved first, then the where clause is evaluated to
filter?

Thanks in advance.

David,

You should post performance related items to the
PERFORMANCE mailing list.

To answer your question a few ideas to try.
I think you only pasted a partial query.
Why do you need the OUTTABLE in the first place?
If you do why not put the WHERE clause inside?

select contact.id
, contact.name
, (select SOMETHING_MISSING from detail inner join
myDetail using
(detail.id) ) as cost
from contact
inner join user using (user_id)
WHERE_CLAUS_MISSING???and user_id = 1
where cost > 30;


Regards,
Nikolaus Dilger


David Wendy wrote:

>
> I have a query of the form:
>
> select * from (
> select contact.id
> , contact.name
> , (select from detail inner join myDetail using
> (detail.id) ) as cost
> from contact
> inner join user using (user_id) and user_id = 1
> ) as OUTTABLE
> where cost > 30;
>
>
> The problem is that all the tables in the query are
> big, so it takes a
> long time to get results from OUTTABLE (around 20
> seconds) . When I
> add the where clause at the end (cost > 30), the query
> becomes too
> long (around 200 seconds sometimes).
>
> To make the query run faster, I thought of creating a
> temp table to
> store OUTTABLE, and then just filter on the temp table
> using cost >
> 30. That would improve the speed of the query
> tremendously (total time
> was around 23 seconds) . The problem with that
approach
> is that I'm
> writing an application, and more than one user might
> need to run the
> query at the same time. (Different user should get
> different results
> because they have different user ids, and other
numbers
> in the query
> would be different). Also, I can't just create and
drop
> temp tables in
> an enterprise application (we are not using ejbs in
our
> applications
> either), it seems like bad style.
>
> Anyhow, how could I make this query faster so that the
> values of
> OUTTABLE is retrieved first, then the where clause is
> evaluated to
> filter?
>
> Thanks in advance.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> <a
href="http://mail.dilger.cc/jump/http://archives.postgresql.org">http://archives.postgresql.org</a>