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

[Jay R. Ashworth]

On Mon, Apr 04, 2005 at 03:37:59PM -0400, Dan Riley wrote:
> "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" ;-)

Careful; that's not me saying that there; in fact, you didn't keep
any of what I said.  Whatever it was.  :-)

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

[ I will read everything before writing anything.  I will read
everything... ]

> 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.

Yeah... but these are all ad-hoc queries, right?  The built in stuff
has been optimized?

Cheers,
-- jra
-- 
Jay R. Ashworth                                                jra at baylink.com
Designer                          Baylink                             RFC 2100
Ashworth & Associates        The Things I Think                        '87 e24
St Petersburg FL USA      http://baylink.pitas.com             +1 727 647 1274

      If you can read this... thank a system administrator.  Or two.  --me