Re: Slow Query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Is there no index on thread.spool? What about notification.user? How about message.time (without thread as a leading column). Those would all seem very significant. Your row counts are very low to have a query perform so badly. Work_mem could probably be increased above 4MB, but it isn't hurting this query in particular.

My primary concern is that the query is rather chaotic at a glance. It would be great to re-write and remove the unneeded keywords, double quotes, totally worthless parentheses, etc. Something like the below may help you see the crux of the query and what could be done and understand how many rows might be coming out of those subqueries. I re-ordered some joins and there might be syntax errors, but give it a shot once you've added the indexes suggested above.

SELECT

spool.id,

handle.handle,

spool.name,

thread.id,

case.closed,

notification.read,

notification2.time,

message.message,

message.time,

message.author,

thread.name,

location.geo 

FROM

spool

JOIN handle ON handle.id = spool.id

JOIN thread ON thread.spool = spool.id

JOIN message ON message.thread = thread.id

LEFT JOIN location ON location.id = spool.location

LEFT JOIN case ON case.id = spool.id 

LEFT JOIN notification ON notification.user = 'b16690e4-a3c5-4868-945e-c2458c27a525'

AND 

notification.id = (

SELECT

notification3.id 

FROM

notification AS notification3

JOIN notification_thread ON notification_thread.id = notification3.id

JOIN thread AS thread2 ON thread2.id = notification_thread.thread 

WHERE

thread2.spool = spool.id

AND notification3.user = 'b16690e4-a3c5-4868-945e-c2458c27a525'

AND notification3.time <= '2020-09-30 16:32:38.054558'

ORDER BY

notification3.time DESC 

LIMIT 1 

)

LEFT JOIN notification AS notification2 ON notification2.user = 'b16690e4-a3c5-4868-945e-c2458c27a525'

AND notification2.id = (

SELECT

notification3.id 

FROM

notification AS notification3

JOIN notification_thread ON notification_thread.id = notification3.id

JOIN thread AS thread2 ON thread2.id = notification_thread.thread 

WHERE

thread2.spool = spool.id

AND notification3.user = 'b16690e4-a3c5-4868-945e-c2458c27a525'

AND notification3.time > '2020-09-30 16:32:38.054558'

ORDER BY

notification3.time DESC 

LIMIT 1 

) 

WHERE

message.time = (

SELECT

MAX ( message2.time ) 

FROM

message AS message2

JOIN thread AS thread2 ON thread2.id = message2.thread

JOIN participant ON participant.thread = thread2.id

JOIN identity ON identity.id = participant.identity

LEFT JOIN relation ON relation.to = identity.id

AND relation.from = 'b16690e4-a3c5-4868-945e-c2458c27a525'

AND relation.manages = TRUE

WHERE

NOT message2.draft 

AND ( identity.id = 'b16690e4-a3c5-4868-945e-c2458c27a525' OR NOT relation.to IS NULL )

AND thread2.spool = spool.id

LIMIT 1

) 

AND notification.id IS NOT NULL

ORDER BY

message.time DESC 

LIMIT 31;


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux