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

On Sun, Aug 14, 2011 at 10:39:48AM -0400, W. Matthew Wilson wrote:
> 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.

That's possible, but you also want to consider using CTEs (common table
expressions). I generally prefer those when my queries are getting too
hairy to read. You'll need PostgreSQL 8.4 or later for those.

See section 7.8 in the manual:
http://www.postgresql.org/docs/current/interactive/queries-with.html

> Is there anything dangerous about making temporary tables in this way?

AFAIK there isn't, but there might be some overhead that you don't get
with CTEs, since a temporary table will probably get materialized on disk
(AFAIK), and the optimizer probably can't do smart things to leave out
rows that cancel out through related WITH blocks.

> 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.

Yeah, ORMs are stupid that way :)

Cheers,
Peter
--
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
is especially attractive, not only because it can be economically
and scientifically rewarding, but also because it can be an aesthetic
experience much like composing poetry or music."
-- Donald Knuth