From: Tyler <tyhou13@gmx.com>
To: pgsql-bugs@lists.postgresql.org
Subject: Column changes such as an increase in varchar size, can cause extremely slow queries, and postgres should run analyze automatically.
Date: Thu, 31 Oct 2024 14:34:38 -0400 (EDT)
Message-ID: <263af164-52f5-4b9c-bac3-5309795fe802@gmx.com> (raw)
In our project lemmy, we recently had a production breaking bug causing extremely slow queries to one of our tables.
Finally after a lot of testing, we narrowed it down to a migration that increased the size of a varchar column meant to store URL data.
This increases the url column from 512 -> 2000 characters.
up.sql:
```sql
ALTER TABLE post
ALTER COLUMN url TYPE varchar(2000);
```
This table currently has 1,186,895 rows, and joins are occasionally done to that column.
We finally realized that running this simple query manually, fixed the issue:
`ANALYZE post (url);`
I'm sure we're not the only ones to experience this potentially production-breaking bug, and postgres should probably automatically re-run analyze on columns for tables that have a large number of rows, that are changed.
For more context, see: https://github.com/LemmyNet/lemmy/pull/5148
Thanks everyone,
-- dessalines
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tyhou13@gmx.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Column changes such as an increase in varchar size, can cause extremely slow queries, and postgres should run analyze automatically.
In-Reply-To: <263af164-52f5-4b9c-bac3-5309795fe802@gmx.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox