[Rt-devel] Question about Type column in table Tickets

[Andrew Sullivan]

On Fri, Jul 01, 2005 at 07:51:57PM +0200, Rolf Grossmann wrote:
> 
> The point is, it doesn't need to use the condition on all records, only 
> those that match the other conditions. Also, for most searches (matching 

Sure doesn't look that way to me, _a priori_.  You happen to know
that once you have the join condition satisfied, the two other
conditions are more or less always true.  But there's no way for a
database to know that in advance.  If I were doing this in Postgres,
I'd try to convince the planner to prefer satisfying the join
condition first, and use partial indexes to show it that many.TYPE =
'ticket' is almost always true (we have used a number of strategies
of this sort to make things go from "uselessly slow" to merely
"sluggish" on a largeish database).  I'm sure that there are similar
knobs to be turned in Oracle, but I'm not familiar enough with Oracle
administration to say what they are. 

> text in some field like in the given query) the database needs to look 
> at every row anyway.

To the extent that's true, I have a feeling that the database design
needs some fixing up.  On any database of any appreciable size, table
scans are going to suck.

> >suggests to me that the query is a bad one, given the structure.  (My
> >experience overall with RT is that the automatic generation of SQL
> >causes all sorts of poorly-written queries like this, that generate
> >needless conditions that slow everything down).
> 
> Like Jesse and I said before, the condition is not needless.

It's not needless in the generic case, but it _happens_ to be
needless here.  From the point of view of supporting largish systems,
using the generic case in every event makes the system hard on the
users.  What they'll do in this case, for instance, since it's taking
too long, is hit "cancel" in their browser and re-submit the query. 
Now you have _two_ expensive queries running.  On a system with 20
users, this quickly becomes a serious problem.

A

-- 
Andrew Sullivan  | ajs at crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what 
you told them to.  That actually seems sort of quaint now.
		--J.D. Baldwin