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