Subtle changes, bugs and feedback

Actually you're only follow 335 classifieds.

Hmm...

SELECT u.parent_item_type_id, u.parent_item_id, COUNT(*) FROM updates u WHERE u.for_profile_id = 47686 GROUP BY u.parent_item_type_id, u.parent_item_id ORDER BY 3 DESC LIMIT 10; parent_item_type_id | parent_item_id | count 
---------------------+----------------+-------
                   0 |              0 |  2242
                   6 |         131364 |   534
                   6 |         172777 |   282
                   6 |         157547 |   122
                   6 |         297404 |   102
                   6 |         252824 |   100
                   6 |         205836 |    94
                   6 |         282374 |    90
                   6 |         252832 |    74
                   6 |         206616 |    73
(10 rows)

There's the bug... when you reply to something it creates a "follow" thing. But it's doing so even when it didn't need to (because one already existed).

The result is that for some items you have hundreds of follows, i.e. on this very thread there are 534 follows.

Actually, I need to work out a way to auto-prune that table... what it is doing isn't bad, but the volume of data it creates is full on crazy.

How strange. If the classifieds aren't the problem then maybe don't nuke them (don't worry if you have already though).
Thanks for the quick replies by the way, and indulging my curiosity!

This sounds like the ~6s delay I get on my Following page. I will watch developments with interest.

It was your post that made me think others were having problems too.

Try it now :)

What did you change?
Just out of technical curiosity

https://github.com/microcosm-cc/microcosm/commit/03f41f95e9039a0f519caefea7d5b1da4e098a8a

It's... weird.

50% came from me changing a JOIN to stop referring to another JOIN table, prevents a huge amount of temporary data.
50% came from moving some fields to the inner SELECTs and removing an outer SELECT and JOIN.

It's pretty trivial stuff though, stuff I would've hoped the optimiser would catch.

Might be easier to read that SQL as one file rather than as a diff of changes:
https://github.com/microcosm-cc/microcosm/blob/master/models/updates.go#L329-L497

--GetUpdates
WITH m AS (
    SELECT m.microcosm_id
      FROM microcosms m
      LEFT JOIN permissions_cache p ON p.site_id = m.site_id
                                   AND p.item_type_id = 2
                                   AND p.item_id = m.microcosm_id
                                   AND p.profile_id = $2
           LEFT JOIN ignores_expanded i ON i.profile_id = $2
                                       AND i.item_type_id = 2
                                       AND i.item_id = m.microcosm_id
     WHERE m.site_id = $1
       AND m.is_deleted IS NOT TRUE
       AND m.is_moderated IS NOT TRUE
       AND i.profile_id IS NULL
       AND (
               (p.can_read IS NOT NULL AND p.can_read IS TRUE)
            OR (get_effective_permissions($1,m.microcosm_id,2,m.microcosm_id,$2)).can_read IS TRUE
           )
)
SELECT total
      ,update_id
      ,for_profile_id
      ,update_type_id
      ,item_type_id
      ,item_id
      ,created_by
      ,created
      ,site_id
      ,has_unread(COALESCE(parent_item_type_id, item_type_id), COALESCE(parent_item_id, item_id), $2)
  FROM (
          SELECT COUNT(*) OVER() AS total
                ,update_id
                ,for_profile_id
                ,update_type_id
                ,item_type_id
                ,item_id
                ,created_by
                ,created
                ,site_id
                ,parent_item_type_id
                ,parent_item_id
            FROM (
                          -- 1;'new_comment';'When a comment has been posted in an item you are watching'
                          -- 4;'new_comment_in_huddle';'When you receive a new comment in a private message'
                          SELECT u.update_id
                                ,u.for_profile_id
                                ,u.update_type_id
                                ,u.item_type_id
                                ,u.item_id
                                ,u.created_by
                                ,u.created
                                ,$1 AS site_id
                                ,f.parent_item_type_id
                                ,f.parent_item_id
                            FROM updates u
                            JOIN flags f ON f.item_type_id = u.item_type_id
                                        AND f.item_id = u.item_id
                                 JOIN (
                                          SELECT MAX(u.update_id) AS update_id
                                                ,f.parent_item_type_id AS item_type_id
                                                ,f.parent_item_id AS item_id
                                            FROM updates u
                                                 JOIN flags f ON f.item_type_id = u.item_type_id
                                                             AND f.item_id = u.item_id
                                            LEFT JOIN ignores i ON i.profile_id = $2
                                                               AND (
                                                                       (i.item_type_id = 3 AND i.item_id = u.created_by)
                                                                    OR (i.item_type_id = f.parent_item_type_id AND i.item_id = f.parent_item_id)
                                                                   )
                                            LEFT JOIN huddle_profiles hp ON hp.huddle_id = f.parent_item_id
                                                                        AND hp.profile_id = u.for_profile_id
                                                                        AND f.parent_item_type_id = 5
                                           WHERE u.for_profile_id = $2
                                             AND i.profile_id IS NULL
                                             AND u.update_type_id IN (1, 4)
                                             AND f.item_is_deleted IS NOT TRUE
                                             AND f.item_is_moderated IS NOT TRUE
                                             AND f.parent_is_deleted IS NOT TRUE
                                             AND f.parent_is_moderated IS NOT TRUE
                                             AND (
                                                     f.microcosm_id IN (SELECT microcosm_id FROM m)
                                                  OR hp.profile_id = u.for_profile_id
                                                 )
                                           GROUP BY f.parent_item_type_id
                                                   ,f.parent_item_id
                                                   ,f.site_id
                                      ) r ON r.update_id = u.update_id
                                 JOIN watchers w ON w.profile_id = $2
                                                AND w.item_type_id = r.item_type_id
                                                AND w.item_id = r.item_id
                           UNION
                          -- 2;'reply_to_comment';'When a comment of yours is replied to'
                          -- 3;'mentioned';'When you are @mentioned in a comment'
                          SELECT u.update_id
                                ,u.for_profile_id
                                ,u.update_type_id
                                ,u.item_type_id
                                ,u.item_id
                                ,u.created_by
                                ,u.created
                                ,$1 AS site_id
                                ,u.parent_item_type_id
                                ,u.parent_item_id
                            FROM updates u
                           WHERE update_id IN (
                                     SELECT MAX(u.update_id)
                                       FROM updates u
                                            JOIN flags f ON f.item_type_id = u.item_type_id
                                                        AND f.item_id = u.item_id
                                            LEFT JOIN huddle_profiles hp ON hp.huddle_id = f.parent_item_id
                                                                        AND hp.profile_id = u.for_profile_id
                                                                        AND f.parent_item_type_id = 5
                                            LEFT JOIN ignores i ON i.profile_id = $2
                                                               AND (
                                                                       (i.item_type_id = 3 AND i.item_id = u.created_by)
                                                                    OR (i.item_type_id = f.parent_item_type_id AND i.item_id = f.parent_item_id)
                                                                   )
                                      WHERE u.for_profile_id = $2
                                        AND i.profile_id IS NULL
                                        AND (u.update_type_id = 2 OR u.update_type_id = 3) -- replies (2) & mentions (3)
                                        AND f.site_id = $1
                                        AND f.item_is_deleted IS NOT TRUE
                                        AND f.item_is_moderated IS NOT TRUE
                                        AND f.parent_is_deleted IS NOT TRUE
                                        AND f.parent_is_moderated IS NOT TRUE
                                        AND (
                                                f.microcosm_id IN (SELECT microcosm_id FROM m)
                                             OR hp.profile_id = u.for_profile_id
                                            )
                                      GROUP BY u.update_type_id
                                              ,u.item_type_id
                                              ,u.item_id
                                     )
                           UNION
                          -- 8;'new_item';'When a new item is created in a microcosm you are watching'
                          SELECT u.update_id
                                ,u.for_profile_id
                                ,u.update_type_id
                                ,u.item_type_id
                                ,u.item_id
                                ,u.created_by
                                ,u.created
                                ,$1 AS site_id
                                ,u.parent_item_type_id
                                ,u.parent_item_id
                            FROM updates u
                           WHERE update_id IN (
                                     SELECT MAX(u.update_id)
                                       FROM updates u
                                            JOIN flags f ON f.item_type_id = u.item_type_id
                                                        AND f.item_id = u.item_id
                                                        AND f.microcosm_id IN (SELECT microcosm_id FROM m)
                                            JOIN watchers w ON w.profile_id = $2
                                                           AND w.item_type_id = 2
                                                           AND w.item_id IN (SELECT microcosm_id FROM m)
                                            LEFT JOIN ignores i ON i.profile_id = $2
                                                               AND i.item_type_id = 3
                                                               AND i.item_id = u.created_by
                                      WHERE u.for_profile_id = $2
                                        AND i.profile_id IS NULL
                                        AND u.update_type_id = 8
                                      GROUP BY u.item_type_id, u.item_id
                                 )
                 ) AS rollup
           ORDER BY created DESC
           LIMIT $3
          OFFSET $4
          ) final_rollup

Oh, and separably the has_unread function had a CASE statement which didn't declare a default ELSE handler but did explicitly handle every scenario it could ever encounter. For whatever reason, the lack of an ELSE sometimes caused the query optimiser to throw an error occasionally.

So a few people may have seen Internal Server Error pages since 8pm yestereve, and it is down to the ELSE not being declared. Fixing that appears to have sped things up a little everywhere.

What postgres version are you running? I have seen some big improvements in query planning and overall performance with 9.6

An older version than that.

I really need to spend time dragging it up a few versions. In my mind I've kinda thought I'll wait until 10 is released as stable, but I should've been updating it before now.

We're on PostgreSQL 9.2 stable.

I feel your pain, we have a few (big,replicated) legacy systems on 9.2 that need upgrading. pg_upgrade helps (and is quick if using hard links), but only for the master - slaves still need data to be rsync'ed.

PG10 does look very cool, logical replication and better control over the planner especially.

Oooh, the things I'll be able to do with these seconds saved!

refresh

refresh

I've actually increased my backup frequency and dropped the slave, in readiness of an upgrade.

We're operating well within the capabilities of a single server with lots of headroom, and I figure once on 10 I can add replication back based on the newer features.

I'm kinda glad this approach is even an option... the work version is on 9.6 but holy moly that was a pain to upgrade to.

3 s, much better! Thanks again.

I'm getting 404 on the second page of my "Following" https://www.lfgss.com/updates/?offset=25

Interesting... maybe my optimisations took away too much safety.

I'll take a look.

I got the same for my Following page 5 yesterday.

Yeah.

I think I know what it is but I couldn't actually check it from here due to LTREE not being compatible with psql command line tool.

I'll fix it when I get home.

Essence: I reduced and simplified some of the SQL JOINs to make it faster, but this may have been too relaxed and it might pull in a few records of items since deleted. After the initial query of "what updates exist" is run, it will fetch the actual items... because some have since been deleted it gets a Not Found for those items, and is then returning that to you.

I think another side effect of the changes is that new threads created in Current Projects (which I'm following) turn up in my Following list but without the "NEW" tag.

I've fixed the 404 Not Found bug with the Following page.

Cannot find an issue with the "NEW" tag.

This thread at the bottom for instance, I've never seen before but it displays like I've read it

1 Attachment

I have a similar thing as @TomvanHalen though classifieds threads. Following page loads much better though!

I had similar on a Rider Down thread today (again, I'm subscribed to all of Rider Down)

So these are all items that appear as a consequence of following a forum, those are the ones for which "NEW" is not showing?