Query planner making bad decisions - Mailing list pgsql-performance

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;


Here is the query run in 12379.816 ms:

 Unique  (cost=1560.06..1560.12 rows=1 width=181) (actual
time=12379.573..12379.642 rows=13 loops=1)
   ->  Sort  (cost=1560.06..1560.06 rows=1 width=181) (actual
time=12379.568..12379.586 rows=13 loops=1)
         Sort Key: main.id, main.effectiveid, main.queue, main."type",
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
         ->  Nested Loop  (cost=0.00..1560.05 rows=1 width=181) (actual
time=9081.782..12379.303 rows=13 loops=1)
               Join Filter: ("outer".objectid = "inner".id)
               ->  Nested Loop  (cost=0.00..849.90 rows=1 width=8)
(actual time=9059.881..12052.548 rows=13 loops=1)
                     Join Filter: ("outer".objectid = "inner".objectid)
                     ->  Nested Loop  (cost=0.00..424.19 rows=1 width=4)
(actual time=0.274..26.660 rows=1575 loops=1)
                           ->  Index Scan using customfields_pkey on
customfields customfields_1  (cost=0.00..16.41 rows=1 width=4) (actual
time=0.228..0.371 rows=1 loops=1)
                                 Filter: ((name)::text = 'QA Origin'::text)
                           ->  Index Scan using ticketcustomfieldvalues2
on objectcustomfieldvalues objectcustomfieldvalues_2  (cost=0.00..407.76
rows=1 width=8) (actual time=0.039..21.243 rows=1575 loops=1)
                                 Index Cond:
(objectcustomfieldvalues_2.customfield = "outer".id)
                                 Filter: ((disabled = 0) AND
((objecttype)::text = 'RT::Ticket'::text) AND ((content)::text ~~
'%Patient Sat Survey%'::text))
                     ->  Nested Loop  (cost=0.00..424.99 rows=58
width=4) (actual time=5.188..7.605 rows=18 loops=1575)
                           ->  Index Scan using customfields_pkey on
customfields customfields_3  (cost=0.00..16.41 rows=1 width=4) (actual
time=0.235..0.419 rows=1 loops=1575)
                                 Filter: ((name)::text = 'QA Group
Code'::text)
                           ->  Index Scan using ticketcustomfieldvalues2
on objectcustomfieldvalues objectcustomfieldvalues_4  (cost=0.00..407.76
rows=65 width=8) (actual time=4.947..7.130 rows=18 loops=1575)
                                 Index Cond:
(objectcustomfieldvalues_4.customfield = "outer".id)
                                 Filter: ((disabled = 0) AND
((objecttype)::text = 'RT::Ticket'::text) AND ((content)::text ~~
'%MOT%'::text))
               ->  Index Scan using tickets1 on tickets main
(cost=0.00..709.77 rows=30 width=181) (actual time=0.020..17.104
rows=5743 loops=13)
                     Index Cond: (queue = 60)
                     Filter: (((status)::text <> 'deleted'::text) AND
(effectiveid = id) AND (("type")::text = 'ticket'::text))
 Total runtime: 12379.816 ms
(23 rows)


select attname,n_distinct from pg_stats where tablename='tickets';
     attname     | n_distinct
-----------------+------------
 id              |         -1
 effectiveid     |  -0.968462
 queue           |         37
 type            |          1
 issuestatement  |          1
 resolution      |          1
 owner           |        123
 subject         |  -0.885148
 initialpriority |         12
 finalpriority   |          9
 priority        |         43
 timeestimated   |          5
 timeworked      |          5
 status          |          6
 timeleft        |          3
 told            |  -0.128088
 starts          |         60
 started         |  -0.862352
 due             |       1270
 resolved        |   -0.94381
 lastupdatedby   |        366
 lastupdated     |   -0.98511
 creator         |       1054
 created         |  -0.965858
 disabled        |          1
(25 rows)


select attname,n_distinct from pg_stats where tablename='customfields';
    attname    | n_distinct
---------------+------------
 id            |         -1
 name          |   -0.83855
 type          |          3
 description   |  -0.202636
 sortorder     |  -0.110379
 creator       |          4
 created       |  -0.739703
 lastupdatedby |          4
 lastupdated   |   -0.78089
 disabled      |          2
 lookuptype    |          1
 repeated      |          1
 pattern       |          1
 maxvalues     |          2
(14 rows)


select attname,n_distinct from pg_stats where
tablename='objectcustomfieldvalues';
     attname     | n_distinct
-----------------+------------
 id              |         -1
 objectid        |      95048
 customfield     |        543
 content         |      30209
 creator         |        115
 created         |  -0.268605
 lastupdatedby   |        115
 lastupdated     |   -0.26863
 objecttype      |          1
 largecontent    |         -1
 contenttype     |          1
 contentencoding |          2
 sortorder       |          1
 disabled        |          2
(14 rows)



If I now analyze objectcustomfieldvalues the query now takes 51747.341 ms:

 Unique  (cost=1564.95..1565.02 rows=1 width=181) (actual
time=51747.087..51747.152 rows=13 loops=1)
   ->  Sort  (cost=1564.95..1564.95 rows=1 width=181) (actual
time=51747.083..51747.097 rows=13 loops=1)
         Sort Key: main.id, main.effectiveid, main.queue, main."type",
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
         ->  Nested Loop  (cost=0.00..1564.94 rows=1 width=181) (actual
time=38871.343..51746.920 rows=13 loops=1)
               Join Filter: ("inner".objectid = "outer".id)
               ->  Nested Loop  (cost=0.00..1136.77 rows=1 width=185)
(actual time=7.772..39862.238 rows=1548 loops=1)
                     Join Filter: ("outer".objectid = "inner".id)
                     ->  Nested Loop  (cost=0.00..426.63 rows=1 width=4)
(actual time=0.266..27.745 rows=1575 loops=1)
                           ->  Index Scan using customfields_pkey on
customfields customfields_1  (cost=0.00..16.41 rows=1 width=4) (actual
time=0.219..0.404 rows=1 loops=1)
                                 Filter: ((name)::text = 'QA Origin'::text)
                           ->  Index Scan using ticketcustomfieldvalues2
on objectcustomfieldvalues objectcustomfieldvalues_2  (cost=0.00..410.20
rows=1 width=8) (actual time=0.040..22.006 rows=1575 loops=1)
                                 Index Cond:
(objectcustomfieldvalues_2.customfield = "outer".id)
                                 Filter: ((disabled = 0) AND
((objecttype)::text = 'RT::Ticket'::text) AND ((content)::text ~~
'%Patient Sat Survey%'::text))
                     ->  Index Scan using tickets1 on tickets main
(cost=0.00..709.77 rows=30 width=181) (actual time=0.015..17.164
rows=5743 loops=1575)
                           Index Cond: (queue = 60)
                           Filter: (((status)::text <> 'deleted'::text)
AND (effectiveid = id) AND (("type")::text = 'ticket'::text))
               ->  Nested Loop  (cost=0.00..427.44 rows=58 width=4)
(actual time=5.207..7.646 rows=18 loops=1548)
                     ->  Index Scan using customfields_pkey on
customfields customfields_3  (cost=0.00..16.41 rows=1 width=4) (actual
time=0.242..0.434 rows=1 loops=1548)
                           Filter: ((name)::text = 'QA Group Code'::text)
                     ->  Index Scan using ticketcustomfieldvalues2 on
objectcustomfieldvalues objectcustomfieldvalues_4  (cost=0.00..410.20
rows=66 width=8) (actual time=4.958..7.154 rows=18 loops=1548)
                           Index Cond:
(objectcustomfieldvalues_4.customfield = "outer".id)
                           Filter: ((disabled = 0) AND
((objecttype)::text = 'RT::Ticket'::text) AND ((content)::text ~~
'%MOT%'::text))
 Total runtime: 51747.341 ms
(23 rows)


select attname,n_distinct from pg_stats where
tablename='objectcustomfieldvalues';
     attname     | n_distinct
-----------------+------------
 id              |         -1
 objectid        |      95017
 customfield     |        539
 content         |      30287
 creator         |        114
 created         |  -0.268403
 lastupdatedby   |        114
 lastupdated     |  -0.268809
 objecttype      |          1
 largecontent    |         -1
 contenttype     |          1
 contentencoding |          2
 sortorder       |          1
 disabled        |          2
(14 rows)


Even better yet, if I turn off enable_nestloop the query runs in
3499.970 ms:

 Unique  (cost=53860.11..53860.18 rows=1 width=181) (actual
time=3499.614..3499.684 rows=13 loops=1)
   ->  Sort  (cost=53860.11..53860.11 rows=1 width=181) (actual
time=3499.608..3499.627 rows=13 loops=1)
         Sort Key: main.id, main.effectiveid, main.queue, main."type",
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
         ->  Hash Join  (cost=27240.41..53860.10 rows=1 width=181)
(actual time=3429.166..3499.538 rows=13 loops=1)
               Hash Cond: ("outer".objectid = "inner".id)
               ->  Merge Join  (cost=0.00..26619.39 rows=58 width=4)
(actual time=1666.503..1736.814 rows=18 loops=1)
                     Merge Cond: ("outer".id = "inner".customfield)
                     ->  Index Scan using customfields_pkey on
customfields customfields_3  (cost=0.00..16.41 rows=1 width=4) (actual
time=0.221..0.410 rows=1 loops=1)
                           Filter: ((name)::text = 'QA Group Code'::text)
                     ->  Index Scan using ticketcustomfieldvalues2 on
objectcustomfieldvalues objectcustomfieldvalues_4  (cost=0.00..26514.04
rows=35342 width=8) (actual time=98.035..1736.277 rows=44 loops=1)
                           Filter: ((disabled = 0) AND
((objecttype)::text = 'RT::Ticket'::text) AND ((content)::text ~~
'%MOT%'::text))
               ->  Hash  (cost=27240.40..27240.40 rows=1 width=185)
(actual time=1762.572..1762.572 rows=1548 loops=1)
                     ->  Hash Join  (cost=26530.47..27240.40 rows=1
width=185) (actual time=1728.887..1758.147 rows=1548 loops=1)
                           Hash Cond: ("outer".id = "inner".objectid)
                           ->  Index Scan using tickets1 on tickets
main  (cost=0.00..709.77 rows=30 width=181) (actual time=0.024..17.550
rows=5743 loops=1)
                                 Index Cond: (queue = 60)
                                 Filter: (((status)::text <>
'deleted'::text) AND (effectiveid = id) AND (("type")::text =
'ticket'::text))
                           ->  Hash  (cost=26530.47..26530.47 rows=1
width=4) (actual time=1728.787..1728.787 rows=1575 loops=1)
                                 ->  Merge Join  (cost=0.00..26530.47
rows=1 width=4) (actual time=1493.343..1726.020 rows=1575 loops=1)
                                       Merge Cond: ("outer".id =
"inner".customfield)
                                       ->  Index Scan using
customfields_pkey on customfields customfields_1  (cost=0.00..16.41
rows=1 width=4) (actual time=0.237..0.429 rows=1 loops=1)
                                             Filter: ((name)::text = 'QA
Origin'::text)
                                       ->  Index Scan using
ticketcustomfieldvalues2 on objectcustomfieldvalues
objectcustomfieldvalues_2  (cost=0.00..26514.04 rows=1 width=8) (actual
time=1493.091..1721.155 rows=1575 loops=1)
                                             Filter: ((disabled = 0) AND
((objecttype)::text = 'RT::Ticket'::text) AND ((content)::text ~~
'%Patient Sat Survey%'::text))
 Total runtime: 3499.970 ms
(25 rows)




Is there anything I can do to improve this?


~Cory Coager



------------------------------------------------------------------------
The information contained in this communication is intended
only for the use of the recipient(s) named above. It may
contain information that is privileged or confidential, and
may be protected by State and/or Federal Regulations. If
the reader of this message is not the intended recipient,
you are hereby notified that any dissemination,
distribution, or copying of this communication, or any of
its contents, is strictly prohibited. If you have received
this communication in error, please return it to the sender
immediately and delete the original message and any copy
of it from your computer system. If you have any questions
concerning this message, please contact the sender.
------------------------------------------------------------------------