[rt-devel] Postgres performance.

[Jesse Vincent]

What aidan is working on is recasting all the joins as explicit joins. 
Because of the toolkit we use to do the dynamic generation, it's a
single code path to change.



On Wed, Apr 09, 2003 at 10:02:57PM -0400, Greg Stark wrote:
> David Wheeler <david at kineticode.com> writes:
> 
> > On Wednesday, April 9, 2003, at 05:08  AM, Aidan Mountford wrote:
> > 
> > > Unless anyone can see a reason why I should stop this, I will carry on
> > > down this path..
> > 
> > I, for one, would welcome the increased performance. It's pretty bad right
> > now.
> 
> Aren't these queries dynamically generated? How can you rewrite them like this
> in a general way?
> 
> There is one disadvantage of explicit joins like you're using: They restrict
> postgres's degrees of freedom which in theory reduces postgres's ability to
> optimize.
> 
> That may sound silly given the speed difference you're seeing, but numbers can
> be deceiving. Most of that difference is coming directly from a single tricky
> join clause. If you can really play with the query you could try simply moving
> the groups_2.id = whatever.id clause outside the OR.
> 
> Also, you may want to try the queries with the CVS version of postgres. It
> will treat the explicit paths very differently from the way 7.3 does and might
> not do exactly what you expect.
> 
> That said, I usually prefer explicit joins myself because I find them
> infinitely more readable than old style queries. But I would suggest ordering
> the tables the way you want them so you don't need the 10 nested
> parentheses...
> 
> I'm still curious why postgres is having trouble with that OR clause. I can't
> seem to come up with any simple example queries that trigger that behaviour.
> By all means consider sending it on to postgres-general, sending there
> generally elicits helpful contributions. I would mention the groups table and
> point out the relevant clause of the query specifically.
> 
> --
> greg
> 
> _______________________________________________
> rt-devel mailing list
> rt-devel at lists.fsck.com
> http://lists.fsck.com/mailman/listinfo/rt-devel

-- 
http://www.bestpractical.com/rt  -- Trouble Ticketing. Free.