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(dot)m(dot)guerrero(at)usit(dot)uio(dot)no>
Center for Information Technology Services
University of Oslo, Norway

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