Re: Query planner making bad decisions

Cory Coager wrote:
> I'm running version 8.1.11 on SLES 10 SP2.  I'm trying to improve this
> query and unfortunately I cannot change the application.  For some
> reason the planner is making a bad decision sometimes after an analyze
> of table objectcustomfieldvalues.
>
> The query is:
> SELECT DISTINCT main.* FROM Tickets main JOIN CustomFields
> CustomFields_1  ON ( CustomFields_1.Name = 'QA Origin' ) JOIN
> CustomFields CustomFields_3  ON (CustomFields_3.Name = 'QA Group Code' )
> JOIN ObjectCustomFieldValues ObjectCustomFieldValues_4  ON
> (ObjectCustomFieldValues_4.ObjectId = main.id ) AND (
> ObjectCustomFieldValues_4.Disabled = '0' ) AND
> (ObjectCustomFieldValues_4.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_4.CustomField = CustomFields_3.id ) JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_2  ON (
> ObjectCustomFieldValues_2.Disabled = '0' ) AND
> (ObjectCustomFieldValues_2.ObjectId = main.id ) AND (
> ObjectCustomFieldValues_2.CustomField = CustomFields_1.id ) AND
> (ObjectCustomFieldValues_2.ObjectType = 'RT::Ticket' )  WHERE
> (main.Status != 'deleted') AND (main.Queue = '60' AND
> ObjectCustomFieldValues_2.Content LIKE '%Patient Sat Survey%' AND
> ObjectCustomFieldValues_4.Content LIKE'%MOT%') AND (main.EffectiveId =
> main.id) AND (main.Type = 'ticket')  ORDER BY main.id ASC;
>
>

Hello

Just in case you want this information. Our RT installation running on
8.3.6 / RHEL4 and with default_statistics_target=100 gives us this query
plan:

Unique  (cost=1360.05..1360.12 rows=1 width=161) (actual
time=2141.834..2141.834 rows=0 loops=1)
   ->  Sort  (cost=1360.05..1360.06 rows=1 width=161) (actual
time=2141.831..2141.831 rows=0 loops=1)
         Sort Key: main.effectiveid, main.issuestatement,
main.resolution, main.owner, main.subject, main.initialpriority,
main.finalpriority, main.priority, main.timeestimated, main.timeworked,
main.status, main.timeleft, main.told, main.starts, main.started,
main.due, main.resolved, main.lastupdatedby, main.lastupdated,
main.creator, main.created, main.disabled
         Sort Method:  quicksort  Memory: 25kB
         ->  Nested Loop  (cost=14.14..1360.04 rows=1 width=161) (actual
time=2141.724..2141.724 rows=0 loops=1)
               ->  Nested Loop  (cost=14.14..1358.09 rows=1 width=165)
(actual time=2141.717..2141.717 rows=0 loops=1)
                     ->  Nested Loop  (cost=14.14..1356.14 rows=1
width=169) (actual time=2141.715..2141.715 rows=0 loops=1)
                           ->  Nested Loop  (cost=14.14..1348.69 rows=1
width=169) (actual time=2141.711..2141.711 rows=0 loops=1)
                                 ->  Bitmap Heap Scan on tickets main
(cost=14.14..1333.78 rows=2 width=161) (actual time=0.906..26.413
rows=1046 loops=1)
                                       Recheck Cond: (queue = 60)
                                       Filter: (((status)::text <>
'deleted'::text) AND (effectiveid = id) AND ((type)::text = 'ticket'::text))
                                       ->  Bitmap Index Scan on tickets1
 (cost=0.00..14.14 rows=781 width=0) (actual time=0.662..0.662 rows=1188
loops=1)
                                             Index Cond: (queue = 60)
                                 ->  Index Scan using
objectcustomfieldvalues3 on objectcustomfieldvalues
objectcustomfieldvalues_2  (cost=0.00..7.44 rows=1 width=8) (actual
time=2.017..2.017 rows=0 loops=1046)
                                       Index Cond:
((objectcustomfieldvalues_2.disabled = 0) AND
(objectcustomfieldvalues_2.objectid = main.effectiveid) AND
((objectcustomfieldvalues_2.objecttype)::text = 'RT::Ticket'::text))
                                       Filter:
((objectcustomfieldvalues_2.content)::text ~~ '%Patient Sat Survey%'::text)
                           ->  Index Scan using objectcustomfieldvalues3
on objectcustomfieldvalues objectcustomfieldvalues_4  (cost=0.00..7.44
rows=1 width=8) (never executed)
                                 Index Cond:
((objectcustomfieldvalues_4.disabled = 0) AND
(objectcustomfieldvalues_4.objectid = main.effectiveid) AND
((objectcustomfieldvalues_4.objecttype)::text = 'RT::Ticket'::text))
                                 Filter:
((objectcustomfieldvalues_4.content)::text ~~ '%MOT%'::text)
                     ->  Index Scan using customfields_pkey on
customfields customfields_3  (cost=0.00..1.94 rows=1 width=4) (never
executed)
                           Index Cond: (customfields_3.id =
objectcustomfieldvalues_4.customfield)
                           Filter: ((customfields_3.name)::text = 'QA
Group Code'::text)
               ->  Index Scan using customfields_pkey on customfields
customfields_1  (cost=0.00..1.94 rows=1 width=4) (never executed)
                     Index Cond: (customfields_1.id =
objectcustomfieldvalues_2.customfield)
                     Filter: ((customfields_1.name)::text = 'QA
Origin'::text)
 Total runtime: 2142.347 ms
(26 rows)

--
 Rafael Martinez, <r.m.guerrero@usit.uio.no>
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/