Message-ID: <43ed5a72fdfdd44fa046d8911c496364a3eb9231.camel@cybertec.at>
Subject: Re: Performance loss after upgrading from 12.15 to 17.2
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Tobias =?ISO-8859-1?Q?Orlam=FCnde?= <postgres@linux-guru.com>,
	pgsql-performance@lists.postgresql.org
Date: Mon, 03 Feb 2025 16:59:21 +0100
In-Reply-To: <4450d336-5981-49ee-b225-70d0fd39fc31@linux-guru.com>
References: <4450d336-5981-49ee-b225-70d0fd39fc31@linux-guru.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/43ed5a72fdfdd44fa046d8911c496364a3eb9231.camel%40cybertec.at>
Precedence: bulk

On Mon, 2025-02-03 at 15:11 +0100, Tobias Orlam=C3=BCnde wrote:
> We are currently in the process of upgrading from 12.15 with Timescale=
=20
> 2.11 to 17.2 with Timescale 2.17.2
> On our pre-prod env we have already upgraded and noticed a remarkable=20
> performance issue vs. the so far not upgraded production environment.
> If we run the exact same query in our pre-rpod env, the execution time=
=20
> increased from ~ 250 ms to over 377k ms.
> Changing random_page_cost from 1.1 to 4 does not help, but changing=20
> work_mem from 64 MB to 256 MB helps, whereas any value lower than 256 MB=
=20
> does not help. The prod server is configured with work_mem =3D 50 MB and=
=20
> is executing the query in 13 ms
>=20
> The Hypertable has been created on column received_time with dimension Ti=
me.
>=20
> In general, we see, that, if we are lowering the filter period of=20
> tick.received_time to ~ 14 days, the query is perfomant (on pre-prod).=
=20
> In prod, neither in- nor de-creasing this does significantly change the=
=20
> execution time.
>=20
> My first assumption is, that somehow accessing the compressed chunks is=
=20
> eating up all the time (which we could also see in the below's explain=
=20
> statements). I somehow tend to point to the compression methods which,=
=20
> IIRC, significantly changed from 12 to 17.
>=20
> Maybe someone could have a look into this and guide me to the right spot=
=20
> for further examination or even solving this issue?

The difference is here (there are two instances per query):

Bad:

  ->  Seq Scan on compress_hyper_6_106_chunk (cost=3D0.00..6428297.17 rows=
=3D2518 width=3D321) (actual time=3D196292.784..196292.784 rows=3D0 loops=
=3D1)
        Filter: ((_ts_meta_min_1 <=3D '2025-01-29 14:31:36'::timestamp with=
out time zone) AND (_ts_meta_max_1 > '2025-01-01 14:31:36'::timestamp witho=
ut time zone) AND ((xx_id)::text =3D
'XS2991917530'::text))
        Rows Removed by Filter: 30492771
        Buffers: shared read=3D5894720

Good:

  ->  Index Scan using compress_hyper_6_106_chunk_xx_id_xx_feed_id__ts_meta=
_min_1_idx on compress_hyper_6_106_chunk  (cost=3D0.56..1571.33 rows=3D2518=
 width=3D321) (actual time=3D0.010..0.010 rows=3D0 loops=3D1)
        Index Cond: (((xx_id)::text =3D 'XS2991917530'::text) AND (_ts_meta=
_min_1 <=3D '2025-01-29 14:31:36'::timestamp without time zone) AND (_ts_me=
ta_max_1 > '2025-01-01 14:31:36'::timestamp without
time zone))
        Buffers: shared hit=3D4

As a first measure, I would run

   ANALYZE compress_hyper_6_106_chunk;

or analyze the partitioned table.  It might well be that the statistics are=
 off.

If that doesn't help, it would be interesting to run the query with the low=
 "work_mem"
setting, but with "enable_seqscan =3D off".

- Does PostgreSQL choose the correct index then?
- What are the cost estimates for the index scan?

Yours,
Laurenz Albe
--=20
Cybertec | https://www.cybertec-postgresql.com