Column changes such as an increase in varchar size, can cause extremely slow queries, and postgres should run analyze automatically.

Поиск
Список
Период
Сортировка
От Tyler
Тема Column changes such as an increase in varchar size, can cause extremely slow queries, and postgres should run analyze automatically.
Дата
Msg-id 263af164-52f5-4b9c-bac3-5309795fe802@gmx.com
обсуждение исходный текст
Ответы Re: Column changes such as an increase in varchar size, can cause extremely slow queries, and postgres should run analyze automatically.
Список pgsql-bugs
Дерево обсуждения
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

В списке pgsql-bugs по дате отправления: