Query organization question - Mailing list pgsql-general

From Gauthier, Dave
Subject Query organization question
Date
Msg-id 482E80323A35A54498B8B70FF2B879800400E39E32@azsmsx504.amr.corp.intel.com
Whole thread Raw
Responses Re: Query organization question
List pgsql-general

I have a poorly performing query that looks something like....

 

select x.name, x.title, x.value

from

  (select a.name as name, b.book_title as title, c.cost as value from ......) x

where

  exists (select ‘found_it’ from get_jobs(x.name) j where j.job = ‘carpenter’);

 

I did it this way because I was hoping that it would generate all the records in the... (select a.name as name, b.book_title as title, c.cost as value from ......) x  first, and then run x.name into the get_jobs stored procedure later.  In fact, running...  (select a.name as name, b.book_title as title, c.cost as value from ......) x  alone runs fast enough, and then running each of the returned x.name values through get_jobs manually runs fast too. But “explain” seems to indicate that it’s “relocating” that get_jobs stored procedure call inside the  (select a.name as name, b.book_title as title, c.cost as value from ......) x , which gives terrible performance.

 

Is there a way I can force it to get the results from (select a.name as name, b.book_title as title, c.cost as value from ......) x  before runing into the stored procedure?

 

BTW, what’s the formal name for the (select a.name as name, b.book_title as title, c.cost as value from ......) x piece of a query like this?

 

Thanks

-dave

pgsql-general by date:

Previous
From: Jeff Soules
Date:
Subject: Re: PostgreSQL Object-Oriented Database?
Next
From: marcin mank
Date:
Subject: Re: PostgreSQL Object-Oriented Database?