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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
