Thread: constantly updated table in Amazon RDS
Hello,
A table is constantly updated by inserting new rows. Will it affect select if where clause is based on > date?
Does "where clause" to specify > date and < now?
Thanks!
On Fri, Oct 21, 2022 at 1:52 PM Julie Nishimura <juliezain@hotmail.com> wrote:
Hello,A table is constantly updated by inserting new rows. Will it affect select if where clause is based on > date?Does "where clause" to specify > date and < now?
You haven't described the table. Is there some kind of a date or timestamp field that is included in new records? Otherwise, how do you know when they were inserted?
A query has a temporarily stable (or consistent) database, inserts, deletes or updates that occur during the processing of that query (or its transaction block, if applicable) are ignored. I'm not sure if that answers your question.
--
Mike Nolan
On Fri, 2022-10-21 at 18:51 +0000, Julie Nishimura wrote: > A table is constantly updated by inserting new rows. Will it affect select if where clause is based on > date? > Does "where clause" to specify > date and < now? Potentially yes, if the rows you query are the most recent rows, for which PostgreSQL has the least accurate statistics. There is a heuristics in the PostgreSQL optimizer: it looks for the actual maximal value to adjust the last histogram bucket boundary. If that is not enough, you can get PostgreSQL to gather table statistics more often by lowering "autovacuum_analyze_scale_factor" for that table: ALTER TABLE tab SET (autovacuum_analyze_scale_factor = 0.01); You'd have to experiment for the best value. Yours, Laurenz Albe
Thank you all
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Friday, October 21, 2022 8:56 PM
To: Julie Nishimura <juliezain@hotmail.com>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Subject: Re: constantly updated table in Amazon RDS
Sent: Friday, October 21, 2022 8:56 PM
To: Julie Nishimura <juliezain@hotmail.com>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Subject: Re: constantly updated table in Amazon RDS
On Fri, 2022-10-21 at 18:51 +0000, Julie Nishimura wrote:
> A table is constantly updated by inserting new rows. Will it affect select if where clause is based on > date?
> Does "where clause" to specify > date and < now?
Potentially yes, if the rows you query are the most recent rows, for which PostgreSQL
has the least accurate statistics. There is a heuristics in the PostgreSQL optimizer:
it looks for the actual maximal value to adjust the last histogram bucket boundary.
If that is not enough, you can get PostgreSQL to gather table statistics more often
by lowering "autovacuum_analyze_scale_factor" for that table:
ALTER TABLE tab SET (autovacuum_analyze_scale_factor = 0.01);
You'd have to experiment for the best value.
Yours,
Laurenz Albe
> A table is constantly updated by inserting new rows. Will it affect select if where clause is based on > date?
> Does "where clause" to specify > date and < now?
Potentially yes, if the rows you query are the most recent rows, for which PostgreSQL
has the least accurate statistics. There is a heuristics in the PostgreSQL optimizer:
it looks for the actual maximal value to adjust the last histogram bucket boundary.
If that is not enough, you can get PostgreSQL to gather table statistics more often
by lowering "autovacuum_analyze_scale_factor" for that table:
ALTER TABLE tab SET (autovacuum_analyze_scale_factor = 0.01);
You'd have to experiment for the best value.
Yours,
Laurenz Albe