Re: Slow Query

Michael Lewis <mlewis@xxxxxxxxxxx> · Wed, 14 Oct 2020 13:17:51 -0600

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;