From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Julie Nishimura <juliezain(at)hotmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: constantly updated table in Amazon RDS |
Date: | 2022-10-22 03:56:20 |
Message-ID: | 19ee98cd64c022b906d11e690bbff012ef71baff.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Julie Nishimura | 2022-10-22 05:32:43 | Re: constantly updated table in Amazon RDS |
Previous Message | Ron | 2022-10-22 03:07:34 | Re: Asking for existence of a GUI frame work similar to Oracle APEX for PostgreSQL |