koldfront

Switching out connectby() with a CTE #lantern #postgresql

🕒︎ - 2022-05-28

I got annoyed with seeing loglines like this from PostgreSQL:

2022-05-28 01:57:34 CEST [2992011-1] lantern@lantern LOG:  duration: 606.988 ms
  statement: SELECT article.id, [...] ORDER BY date DESC LIMIT 7

Really? 600 ms for just getting the 7 articles for the frontpage? Admittedly including the comment count, but still!

So after finding the obligatory example code on StackOverflow and fighting my way through adapting it to work, I got the query down to a more reasonable ~20 ms.

Also, the query that generates the Archive page was identical (except for the LIMIT (and sort order)), so I refactored it to use the same base SQL-string. This could be improved to only get the fields needed on the page, but then I need to figure out how to have a partial Article data type, and I don't want to do that now.

There are still 3 places I use connectby(); they need to be eliminated as well, but it's late, so not tonight.

One thing I didn't leave for another day, was to get rid of a warning. Phew.

🕒︎ - 2022-05-28 - 1 comment

Adam writes:

There are still 3 places I use connectby(); they need to be eliminated as well, but it's late, so not tonight.

Now they are gone as well!

- Adam Sjøgren 🕛︎ - 2022-05-28

+=

Add comment

To avoid spam many websites make you fill out a CAPTCHA, or log in via an account at a corporation such as Twitter, Facebook, Google or even Microsoft GitHub.

I have chosen to use a more old school method of spam prevention.

To post a comment here, you need to:

¹ Such as Thunderbird, Pan, slrn, tin or Gnus (part of Emacs).

Or, you can fill in this form:

+=