Re: Queries containing ORDER BY and LIMIT started to work slowly

On Tue, Aug 29, 2023 at 3:57 PM Rondat Flyag wrote: > I took the dump just to store it on another storage (external HDD). I > didn't do anything with it. > > 29.08.2023, 21:42, "Jeff Janes" : > > > > On Tue, Aug 29, 2023 at 1:47 PM Rondat Flyag > wrote: > > I have a legacy system that uses

Re: Queries containing ORDER BY and LIMIT started to work slowly

On Tue, Aug 29, 2023 at 2:55 PM Rondat Flyag wrote: > I took the dump just to store it on another storage (external HDD). I > didn't do anything with it. > I don't see how that could cause the problem, it is probably just a coincidence. Maybe taking the dump held a long-lived snapshot open

Re: Index bloat and REINDEX/VACUUM optimization for partial index

On Mon, Aug 28, 2023 at 8:33 PM jayaprabhakar k wrote: > Hi, > > TL;DR: > Observations: > >1. REINDEX requires a full table scan > - Roughly create a new index, rename index, drop old index. > - REINDEX is not incremental. running reindex frequently does not > reduce the

Re: Queries containing ORDER BY and LIMIT started to work slowly

I took the dump just to store it on another storage (external HDD). I didn't do anything with it. 29.08.2023, 21:42, "Jeff Janes" :  On Tue, Aug 29, 2023 at 1:47 PM Rondat Flyag wrote:I have a legacy system that uses `Posgresql 9.6` and `Ubuntu 16.04`. Everything was fine

Re: Queries containing ORDER BY and LIMIT started to work slowly

On Tue, Aug 29, 2023 at 1:47 PM Rondat Flyag wrote: > I have a legacy system that uses `Posgresql 9.6` and `Ubuntu 16.04`. > Everything was fine several days ago even with standard Postgresql > settings. I dumped a database with the compression option (maximum > compression level -Z 9) in order

Queries containing ORDER BY and LIMIT started to work slowly

I have a legacy system that uses `Posgresql 9.6` and `Ubuntu 16.04`. Everything was fine several days ago even with standard Postgresql settings. I dumped a database with the compression option (maximum compression level -Z 9) in order to have a smaller size (`pg_dump --compress=9 database_name >

Re: Index bloat and REINDEX/VACUUM optimization for partial index

Thanks Peter. It is *14.4*, But on AWS RDS Aurora instance. I am trying to read the links you shared - B-Tree Deletion and deduplication, etc. I still don't fully understand what I need to do. In the BTree documentation, > The average and worst-case number of versions per logical row can be kept

Re: Range partitioning query performance with date_trunc (vs timescaledb)

On Tue, 29 Aug 2023 at 19:40, Philippe Pepiot wrote: > I'm trying to implement some range partitioning on timeseries data. But it > looks some queries involving date_trunc() doesn't make use of partitioning. > > BEGIN; > CREATE TABLE test ( > time TIMESTAMP WITHOUT TIME ZONE NOT NULL, >

Range partitioning query performance with date_trunc (vs timescaledb)

Hi, I'm trying to implement some range partitioning on timeseries data. But it looks some queries involving date_trunc() doesn't make use of partitioning. BEGIN; CREATE TABLE test ( time TIMESTAMP WITHOUT TIME ZONE NOT NULL, value FLOAT NOT NULL ) PARTITION BY RANGE (time); CREATE INDEX