[rt-users] *very* slow 'OR' searches

[Dan Riley]

"Jay R. Ashworth" <jra at baylink.com> writes:
> On Sun, Apr 03, 2005 at 12:25:43PM -0600, Michael Loftis wrote:
> > > I find it somewhat worrisome that it is so easy to stumble across a
> > > simple search that takes essentially forever.  Presumably this happens
> > > because neither the query builder nor the MySQL "optimizer" notices
> > 
> > MySQL doesn't have an optimizer, at all. [...]

I must need scarier "scare quotes" ;-)

> Sounds like another good reason to use PgSQL instead.  :-)

PostgreSQL 8.0.1 tries harder, but sadly doesn't actually do any
better.  Here's the skeleton of the query plan for the query produced
by SearchBuilder for "Subject like 'foo' OR Content like 'bar'":

 Limit  (cost=750.26..750.33 rows=1 width=172)
   ->  Unique  (cost=750.26..750.33 rows=1 width=172)
         ->  Sort  (cost=750.26..750.27 rows=1 width=172)
               ->  Nested Loop  (cost=11.88..750.25 rows=1 width=172)
                     ->  Seq Scan on transactions transactions_1
                         (cost=0.00..48.40 rows=211 width=8)
                     ->  Materialize  (cost=11.88..12.97 rows=109 width=335)
                           ->  Nested Loop
                               (cost=0.00..11.77 rows=109 width=335)
                                 ->  Seq Scan on tickets main
                                     (cost=0.00..5.59 rows=1 width=172)
                                 ->  Seq Scan on attachments attachments_2
                                     (cost=0.00..5.09 rows=109 width=163)

and the same for my rewrite:

 Limit  (cost=14.47..14.53 rows=1 width=172)
   ->  Unique  (cost=14.47..14.53 rows=1 width=172)
         ->  Sort  (cost=14.47..14.47 rows=1 width=172)
               ->  Nested Loop  (cost=0.00..14.46 rows=1 width=172)
                     ->  Nested Loop  (cost=0.00..11.43 rows=1 width=176)
                           ->  Seq Scan on tickets main
                               (cost=0.00..5.59 rows=1 width=172)
                           ->  Index Scan using transactions1 
                               (cost=0.00..5.82 rows=1 width=8)
                     ->  Index Scan using attachments2
                         (cost=0.00..3.01 rows=1 width=163)

These were produced after a "vacuum analyze;" and running the queries
(PostgreSQL had an incorrectly optimistic estimate for the first query
until I vacuumed, after which it got more realistic).  I haven't done
any PostgreSQL performance tuning, so I won't make any quantitative
comparisons between the DBs, but the same qualitative relationship
holds--the query produced by SearchBuilder is orders of magnitude
slower than the rewrite.

I found a post to the MySQL MaxDB mailing list from someone running RT
on MaxDB, complaining about the performance of ORs, with a response
from someone at SAP that this is a hard problem, and MaxDB currently
does not optimize ORs--that saved me from trying MaxDB.  I _could_
try Oracle, but it's probably easier to tell our users not to OR
dissimilar types.

-dan