Re: Slow Query - Mailing list pgsql-performance
| From | Michael Lewis |
|---|---|
| Subject | Re: Slow Query |
| Date | |
| Msg-id | CAHOFxGruqLeW4X-mw7tQRdV27X9oouPR4hSCo_-nJW6Yne48yg@mail.gmail.com Whole thread Raw |
| In response to | Slow Query (Parth Shah <parth@polimorphic.com>) |
| Responses |
Re: Slow Query
(Parth Shah <parth@polimorphic.com>)
|
| List | pgsql-performance |
SELECT
handle.handle,
case.closed,
notification.read,
notification2.time,
message.message,
message.time,
message.author,
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
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
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;
pgsql-performance by date: