Speed up a query - using a temp table
Speed up a query - using a temp table
От:
yusuf0478@netscape.net (David Wendy)
Дата:
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.
Re: Speed up a query - using a temp table
От:
"Nikolaus Dilger" <nikolaus@dilger.cc>
Дата:
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? > > http://archives.postgresql.org