[rt-devel] Postgres performance.

[Greg Stark]

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