Re: optimizing common subqueries

You can put the complex query into a subquery in the from clause and
PostgreSQL will normally do a good job with that. See one of the other
replies in this thread.

On Wed, Oct 05, 2005 at 11:23:20AM -0400, John D. Burger wrote:
> As I understand it, Postgres's query planner considers only trees of
> joins - I don't know what the technical implications are of using DAG
> plans, other than the obvious blowup in planning space.
>
> I was recently in a similar situation, where a script essentially
> needed to do a self-join on the result of a complex query.  The script
> uses a temp table to store the results of the first query, and then
> does a second query using the temp table - effectively, I have done
> common-subexpression reduction by hand.  This repeated fragment of your
> example:
>
>      SELECT * FROM common_pmids('mycn','trka')
>
> might be a candidate for such treatment.
>
> - John Burger
>   MITRE
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461