Обсуждение: How to tame a gigantic (100+ lines) query in a web app?
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@tplus1.com http://tplus1.com
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
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. > > 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 > > > I'm a big fan of the temp table plan, even though they are the epitome of the very thing of which you indict ORMs! And you might be wrong on that thesis but who cares, you're in hand-craft mode. My very biased take is that you will at worst break even performance-wise, and probably come out on top, especially if a left join goes through an intersection record.
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
body p { margin-bottom: 0cm; margin-top: 0pt; }
<body style="direction: ltr;"
bidimailui-detected-decoding-type="latin-charset" bgcolor="#ffffff"
text="#000000">
On 08/14/2011 05:39 PM, W. Matthew Wilson wrote:
<blockquote
cite="mid:CAGHfCUDQc8RywD1_qa1_d3K3pFA5mGa4y5dyWh+ysdAgAgzfPg@mail.gmail.com"
type="cite">
Is there anything dangerous about making temporary tables in this way?
Matt
The only dangerous thing I have encountered with temp tables is
that the planner compiles the table oid into the plan and if you
run a function after the table has been dropped and recreated it
will tell you that the table doesn't exist. This is from 8.2 and
lower, I haven't checked if this is still a problem in more recent
versions.
Sim
Hi, use WITH queries, I use this regularly and it works fine. http://www.postgresql.org/docs/9.0/static/queries-with.html regards Thomas Am 14.08.2011 16:39, schrieb W. Matthew Wilson: > 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 > >