How to tame a gigantic (100+ lines) query in a web app?

From: "W(dot) Matthew Wilson" <matt(at)tplus1(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How to tame a gigantic (100+ lines) query in a web app?
Date: 2011-08-14 14:39:48
Message-ID: CAGHfCUDQc8RywD1_qa1_d3K3pFA5mGa4y5dyWh+ysdAgAgzfPg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm sure I'm not the first person to end up with a gigantic query that
does lots of left joins and subselects.

It seems to work, but I would love to break it up into smaller chunks.

I'm thinking about rewriting the query to make several temporary
tables that are dropped on commit, and then joining them at the end.

I can't just use views for everything because I use parameters passed
in from the web app. I am using a few views where I can.

Is there anything dangerous about making temporary tables in this way?
I started two transactions simultaneously and they were both able to
make their own temporary tables.

More generally, how to tame this big ol' query?

The temporary tables mean I'm only pulling data from the database one
time. ORMs often pull data from one query and then use that data to
write the next query. This seems slow to me.

Matt

--
W. Matthew Wilson
matt(at)tplus1(dot)com
http://tplus1.com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Colin Beckingham 2011-08-14 14:45:50 Compile pgadmin1-1.14.0-beta3 - ctlMenuToolbar
Previous Message sad 2011-08-14 12:34:20 Re: Using Postgresql as application server