From: | Michael Lewis <mlewis(at)entrata(dot)com> |
---|---|
To: | Parth Shah <parth(at)polimorphic(dot)com> |
Cc: | postgres performance list <pgsql-performance(at)postgresql(dot)org>, Daniel Smith <daniel(at)polimorphic(dot)com> |
Subject: | Re: Slow Query |
Date: | 2020-10-14 19:17:51 |
Message-ID: | CAHOFxGruqLeW4X-mw7tQRdV27X9oouPR4hSCo_-nJW6Yne48yg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Parth Shah | 2020-10-15 01:11:29 | Re: Slow Query |
Previous Message | Parth Shah | 2020-10-14 17:30:41 | Slow Query |