ldvalues';
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.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
