Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t6asE-00E53z-AR for pgsql-bugs@arkaria.postgresql.org; Thu, 31 Oct 2024 19:30:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1t6asC-005JYt-Jd for pgsql-bugs@arkaria.postgresql.org; Thu, 31 Oct 2024 19:30:24 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t6a0T-004mQv-6g for pgsql-bugs@lists.postgresql.org; Thu, 31 Oct 2024 18:34:53 +0000 Received: from mout.gmx.net ([212.227.17.22]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t6a0K-0042sk-4J for pgsql-bugs@lists.postgresql.org; Thu, 31 Oct 2024 18:34:52 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmx.com; s=s31663417; t=1730399682; x=1731004482; i=tyhou13@gmx.com; bh=Gaz+EBgpaFpGCq7AQmNgeJzCkuqejLMZ7ow6MeFq0TI=; h=X-UI-Sender-Class:Date:From:To:Message-ID:Subject:MIME-Version: Content-Type:cc:content-transfer-encoding:content-type:date:from: message-id:mime-version:reply-to:subject:to; b=szBkdTSgj2IuWXEOFo2hkT+YzDNhWFuV4JlEMy2R5fXLFMojGB5Wzm2nmpr4GF0r 3MVtHe1W5GL87KV23lx5gFyz6g82yE4PxFY+SW6tdyqOoXdI6JVBIi7x/OL41C9Fq xV9KrU5uVSRUm2eG6nIGBA4Uia8PUL4bLkeuTIEmVho0Vl3uLIjWAsfBMP5ipqozs U+W5YTagLQxc6QUvYdNBV462y0tWv6hAqYEnnHfggYTqnzO1gF7SxEQP+3AMRQ24y +WtDdbke2FInzdstCGXxneYwSYe/1klQ0UZpaMyAszSvep7e1ZS04I1tlYssU6wYS l4Rivuc8Oierfpaduw== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [127.0.0.1] ([198.54.135.125]) by mail.gmx.net (mrgmx104 [212.227.17.174]) with ESMTPSA (Nemesis) id 1MtfNf-1tzdGf1FN9-0138TE for ; Thu, 31 Oct 2024 19:34:42 +0100 Date: Thu, 31 Oct 2024 14:34:38 -0400 (EDT) From: Tyler To: pgsql-bugs@lists.postgresql.org Message-ID: <263af164-52f5-4b9c-bac3-5309795fe802@gmx.com> Subject: Column changes such as an increase in varchar size, can cause extremely slow queries, and postgres should run analyze automatically. MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_9_55358336.1730399678414" X-Correlation-ID: <263af164-52f5-4b9c-bac3-5309795fe802@gmx.com> X-Provags-ID: V03:K1:05EOxY/3Zukd++P2APD9cH3ccHkc11rpcdk0dIe9qww/VnzaYEe HmhV6szbmq9UFwKzYhkhEKPg9sz7pZUhfS5WI2SbDiCeOk0T3Lw6m/+3VstN1hoQyNmo35X Jplbcs+7oTOF50dReBNz2a6bC6THrlFoL2xFE0Y6bHc8n+uV7OP2qkWvLdlXXsk2LMOJjVQ dDEDyZUurQv3V/gNPCwww== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:6RD8diPkWT0=;0/JJMAgU3n/A2DHonKE/8Qu7UYx jXPTf0fya6F5Rsv6e7s5G/hkZbOMPisWhUJWcObIsyhudvMf8X3A4T/1BmBaqFSRjpAOErpgt f8eX8oL8tikUul/8SaSrXK1mn+VGgD3elHPjwXeC7W6TQfy71PUlKPPW1t0nFjQWQ2uEQon20 kLoPvJH409ryU7lyhlf1QZFulecIcQcqx+WPNpiPitlQ6eJhDyT/jSrvD4jVxdI5vAozlJ3bM cEfFi/e3TpdSGGv7ie5Udc+/V8DfnhJfk1pIL3fJsTrkCSM4I3CpGOzojJSlPr3DlS4yMUBNU LbW8n1D7L99jq0xlrkWzeo2f8niV4rDub3mIlBKu/NDRe4m1xYXm0WcSwDIw3k8IG+WaCK/a6 g0jyszB414x9LG5W681rxT8TgXV12BYMAyGlS+pN3ZBYKtmrpPCq/KnkRCRQmfI3/PcvRqYrc YrfMA0XRdGzJFKMc7So8RPhM0NSGiyjvg7V1Z+YXVdj4ArHW+cmGl5xt0iuTkU7S3ChR5YQ9U HyWUCAvUbmaBOBb0bLDq2HiVVgwaQpdYoPtiwxvlYpXfRDXaX4Ej2GzF185wfFVU5zNwDA8u2 afUsRk9cibF0ewkcBKhjVsyh6+Ly9qNr/1EcyBkj0+RocXIjUjuumLadzJXGtSX3OtIKZYCMB 90i+zc+LmpyAnLjqGpwTCsO+WAr13AIW3XpIiU0E+lhx4H5ONZM+w4LVsXJlOvWxTzThNVuYV wW4an6j0qscBBqLrk5OmtTHYk5hyWdY3Xwy77UTTObd4SirC3/8jJVCKdQltKv7417Jd10ik7 QaTUM0k9vFmpHvgRrFfbsV+A== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_9_55358336.1730399678414 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable In our project lemmy, we recently had a production breaking bug causing ext= remely slow queries to one of our tables. Finally after a lot of testing, we narrowed it down to a migration that inc= reased 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 =C2=A0=C2=A0=C2=A0 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 issu= e: `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 ------=_Part_9_55358336.1730399678414 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit
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
------=_Part_9_55358336.1730399678414--