these values were collected before vacuum analyse:select (select reltuples
from pg_Class where relName = t.relName), * from pg_stat_sys_tables t where
schemaname = 'pg_catalog' and relname in
('pg_class','pg_attribute','pg_index');
reltuples relid schemaname relname seq_scanseq
On 8/23/19 3:47 PM, PegoraroF10 wrote:
This week we added just 5 new customers. Every schema has 100 tables, 300
indices, 400 triggers.
I cannot imagine our script doing 75000 updates just for adding those
schemas.
Very rough calculation:
https://www.postgresql.org/docs/11/catalog-pg-class.htm
Can you manually execute vacuum analyze on all three involved tables and
again share the plan for the same query? If it is significantly improved,
it would seem like following the recommendation to tune autovacuum (and
analyze) to be more frequent would be prudent.
You haven't seemed to change fro
This week we added just 5 new customers. Every schema has 100 tables, 300
indices, 400 triggers.
I cannot imagine our script doing 75000 updates just for adding those
schemas.
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
autovacuum_analyze_threshold = 50;
autovacuum_analyze_scale_factor = 0.1
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
https://explain.depesz.com/s/5Rrd
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT
current_database() AS TABLE_CATALOG, n.nspname AS TABLE_SCHEMA, t.relname AS
TABLE_NAME, t.oid AS TABLE_OID, current_database() AS INDEX_CATALOG,
n.nspname AS INDEX_SCHEMA, c.relname AS INDEX_NAME, c.oid AS INDEX_O
On 8/23/19 2:47 PM, PegoraroF10 wrote:
Coming back to my problem, today happened again, that search comes slow.
Explaining again, auto vacuum and auto analyse for pg_* tables are not
configured one by one, so its using that defaults of scale_factor = 0.2
and threshold = 50. Today, when that pro
Might the issue be with pg_index or pg_attribute rather than pg_class? Is
the query still slow? Do you have the output of explain analyze for use on
https://explain.depesz.com/ or the output of EXPLAIN (ANALYZE, COSTS,
VERBOSE, BUFFERS, FORMAT JSON) for use on
http://tatiyants.com/pev/#/plans/new w
Coming back to my problem, today happened again, that search comes slow.
Explaining again, auto vacuum and auto analyse for pg_* tables are not
configured one by one, so its using that defaults of scale_factor = 0.2 and
threshold = 50. Today, when that problem of speed came back, the view
pg_stat_s
As I told you before, these queries cannot be changed because the driver
creates them.
As I cannot change them how can I help Postgres to run it faster, just that.
My log_min_duration_statement = 500ms, so I find SQL some dozens of this sql
inside it daily, but I´m sure this sql is used thousands
PegoraroF10 writes:
> Values for autovacuum and autoanalyse are Null because I did not configured
> them for system tables yet, but I´m doing vacuum manually once a week. My
> question now is why those selects varies that way.
Well, one point is that the execution time would probably vary hugely
Well, not exactly.
Values for autovacuum and autoanalyse are Null because I did not configured
them for system tables yet, but I´m doing vacuum manually once a week. My
question now is why those selects varies that way.
Almost all times it spend 20ms but 2 o 3% of the times it spend 500ms, why ?
On 8/15/19 12:09 PM, PegoraroF10 wrote:
last values for autovacuum and autoanalyse are Null.
Thanks, I´ll change and see if that solve our problem
I thought we had been down this trail before:
https://www.postgresql.org/message-id/1564168884421-0.post%40n3.nabble.com
--
Sent from: https:
last values for autovacuum and autoanalyse are Null.
Thanks, I´ll change and see if that solve our problem
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Are you autovacuuming and analyzing aggressively enough? Is there bloat or
other concern for these system tables? I expect it may be a concern based
on what I remember about your environment having thousands of tables and
perhaps autovacuum not keeping up (re: Too slow to create new schema and
thei
The driver I´m using does some queries on system tables and obviously I
cannot change them because are driver inside. These two queries are usually
fast, but sometimes they take 8 or 15 times more to run. The question is,
can I change something on these tables ? Can i create an index, can I load
th
16 matches
Mail list logo
