Thread: comparing output of internal pg tables of referenced tables
Hey,
I tried to get a list of all tables that has a reference to my_table. I used two different queries :
1)select R.*
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS FK
on U.CONSTRAINT_CATALOG = FK.UNIQUE_CONSTRAINT_CATALOG
and U.CONSTRAINT_SCHEMA = FK.UNIQUE_CONSTRAINT_SCHEMA
and U.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE R
ON R.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
AND R.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND R.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
AND U.TABLE_NAME = 'my_table'
2)select conname, (select r.relname from pg_class r where r.oid = c.confrelid) as orig_table, (select array_agg(attname) from pg_attribute where attrelid = c.confrelid and ARRAY[attnum] <@ c.conkey) as orig_cols, (select r.relname from pg_class r where r.oid = c.conrelid) as foreign_table, (select array_agg(attname) from pg_attribute where attrelid = c.conrelid and ARRAY[attnum] <@ c.conkey) as foreign_cols from pg_constraint c where c.confrelid = (select oid from pg_class where relname = 'my_table') and c.contype='f'
On the second output in the orig_cols I got a few weird outputs like : {........pg.dropped.5........} or even a columns that doesnt have a unique index (just a random column from the orig_table).
tried to vacuum the table but still didnt help. The db is at version 9, but I tried to upgrade it to 10/11/12 and in all versions it stayed the same.
Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
> 2)select conname, (select r.relname from pg_class r where r.oid =
> c.confrelid) as orig_table, (select array_agg(attname) from pg_attribute
> where attrelid = c.confrelid and ARRAY[attnum] <@ c.conkey) as
> orig_cols, (select r.relname from pg_class r where r.oid = c.conrelid) as
> foreign_table, (select array_agg(attname) from pg_attribute where
> attrelid = c.conrelid and ARRAY[attnum] <@ c.conkey) as foreign_cols from
> pg_constraint c where c.confrelid = (select oid from pg_class where
> relname = '*my_table*') and c.contype='f'
> On the second output in the orig_cols I got a few weird outputs like
> : {........pg.dropped.5........} or even a columns that doesnt have a
> unique index (just a random column from the orig_table).
You need to be looking at confkey not conkey for the columns in the
confrelid table.
regards, tom lane
Hi!
I have a query that SELECT's only one tuple using a PK (https://explain.depesz.com/s/Hskt)
the field I am selecting are a bigint and a text. Why does it read 1095 shared buffers read?
If I adda LIMIT 1 clause, the query runs much faster: https://explain.depesz.com/s/bSZn
This table has only one tuple anyway, so I can't understand why does it takes so long without the LIMIT 1.
I have a query that SELECT's only one tuple using a PK (https://explain.depesz.com/s/Hskt)
the field I am selecting are a bigint and a text. Why does it read 1095 shared buffers read?
If I adda LIMIT 1 clause, the query runs much faster: https://explain.depesz.com/s/bSZn
This table has only one tuple anyway, so I can't understand why does it takes so long without the LIMIT 1.
Is this result able to be repeated?
Em 19/09/2019 14:21, Michael Lewis escreveu: > Is this result able to be repeated? Yes, I can consistently repeat it. Postgres version is 11.1. Other executions: Index Scan using assessoria_pkey on public.assessoria (cost=0.25..2.47 rows=1 width=62) (actual time=1.591..4.035 rows=1 loops=1) Output: asscod, asscambol Index Cond: (assessoria.asscod = 1) Buffers: shared hit=1187 Planning Time: 0.053 ms Execution Time: 4.055 ms Index Scan using assessoria_pkey on public.assessoria (cost=0.25..2.47 rows=1 width=62) (actual time=1.369..3.838 rows=1 loops=1) Output: asscod, asscambol Index Cond: (assessoria.asscod = 1) Buffers: shared hit=1187 Planning Time: 0.033 ms Execution Time: 3.851 ms
-----Original Message----- From: Luís Roberto Weck [mailto:luisroberto@siscobra.com.br] Sent: Thursday, September 19, 2019 2:30 PM To: Michael Lewis <mlewis@entrata.com> Cc: pgsql-performance@lists.postgresql.org Subject: Re: Slow query on a one-tuple table WARNING: This email originated from outside of Perceptron! Please be mindful of PHISHING and MALWARE risks. Em 19/09/2019 14:21, Michael Lewis escreveu: > Is this result able to be repeated? Yes, I can consistently repeat it. Postgres version is 11.1. Other executions: Index Scan using assessoria_pkey on public.assessoria (cost=0.25..2.47 rows=1 width=62) (actual time=1.591..4.035 rows=1 loops=1) Output: asscod, asscambol Index Cond: (assessoria.asscod = 1) Buffers: shared hit=1187 Planning Time: 0.053 ms Execution Time: 4.055 ms Index Scan using assessoria_pkey on public.assessoria (cost=0.25..2.47 rows=1 width=62) (actual time=1.369..3.838 rows=1 loops=1) Output: asscod, asscambol Index Cond: (assessoria.asscod = 1) Buffers: shared hit=1187 Planning Time: 0.033 ms Execution Time: 3.851 ms ________________________________________________________________________________________________________________ But can you repeat it with "LIMIT 1"? Notice huge difference in "buffers hit" while doing (the same) Index Scan in two plans. Regards, Igor Neyman
Em 19/09/2019 15:34, Igor Neyman escreveu: > -----Original Message----- > From: Luís Roberto Weck [mailto:luisroberto@siscobra.com.br] > Sent: Thursday, September 19, 2019 2:30 PM > To: Michael Lewis <mlewis@entrata.com> > Cc: pgsql-performance@lists.postgresql.org > Subject: Re: Slow query on a one-tuple table > > WARNING: This email originated from outside of Perceptron! Please be mindful of PHISHING and MALWARE risks. > > Em 19/09/2019 14:21, Michael Lewis escreveu: >> Is this result able to be repeated? > Yes, I can consistently repeat it. > > Postgres version is 11.1. > > Other executions: > > Index Scan using assessoria_pkey on public.assessoria (cost=0.25..2.47 > rows=1 width=62) (actual time=1.591..4.035 rows=1 loops=1) > Output: asscod, asscambol > Index Cond: (assessoria.asscod = 1) > Buffers: shared hit=1187 > Planning Time: 0.053 ms > Execution Time: 4.055 ms > > Index Scan using assessoria_pkey on public.assessoria (cost=0.25..2.47 > rows=1 width=62) (actual time=1.369..3.838 rows=1 loops=1) > Output: asscod, asscambol > Index Cond: (assessoria.asscod = 1) > Buffers: shared hit=1187 > Planning Time: 0.033 ms > Execution Time: 3.851 ms > > ________________________________________________________________________________________________________________ > > But can you repeat it with "LIMIT 1"? > Notice huge difference in "buffers hit" while doing (the same) Index Scan in two plans. > > Regards, > Igor Neyman With LIMIT 1, I get 3 shared buffers hit, pretty much always.
With LIMIT 1, I get 3 shared buffers hit, pretty much always. ____________________________________________________________________________________ Check if assessoria_pkey index is bloated. Regards, Igor Neyman
Em 19/09/2019 17:11, Igor Neyman escreveu: > With LIMIT 1, I get 3 shared buffers hit, pretty much always. > > ____________________________________________________________________________________ > > Check if assessoria_pkey index is bloated. > > Regards, > Igor Neyman > > With this query[1] it shows: current_database|schemaname|tblname |idxname |real_size|extra_size|extra_ratio|fillfactor|bloat_size|bloat_ratio|is_na| ----------------|----------|----------|---------------|---------|----------|-----------|----------|----------|-----------|-----| database_name |public |assessoria|assessoria_pkey| 16384| 0| 0.0| 90| 0.0| 0.0|false| [1]https://github.com/ioguix/pgsql-bloat-estimation/blob/master/btree/btree_bloat-superuser.sql
Em 19/09/2019 17:24, Luís Roberto Weck escreveu: > Em 19/09/2019 17:11, Igor Neyman escreveu: >> With LIMIT 1, I get 3 shared buffers hit, pretty much always. >> >> ____________________________________________________________________________________ >> >> >> Check if assessoria_pkey index is bloated. >> >> Regards, >> Igor Neyman >> >> > > With this query[1] it shows: > > current_database|schemaname|tblname |idxname > |real_size|extra_size|extra_ratio|fillfactor|bloat_size|bloat_ratio|is_na| > ----------------|----------|----------|---------------|---------|----------|-----------|----------|----------|-----------|-----| > > database_name |public |assessoria|assessoria_pkey| 16384| > 0| 0.0| 90| 0.0| 0.0|false| > > [1]https://github.com/ioguix/pgsql-bloat-estimation/blob/master/btree/btree_bloat-superuser.sql > > > Using the quer provided here[1] I see this comment: /* * distinct_real_item_keys is how many distinct "data" fields on page * (excludes highkey). * * If this is less than distinct_block_pointers on an internal page, that * means that there are so many duplicates in its children that there are * duplicate high keys in children, so the index is probably pretty bloated. * * Even unique indexes can have duplicates. It's sometimes interesting to * watch out for how many distinct real items there are within leaf pages, * compared to the number of live items, or total number of items. Ideally, * these will all be exactly the same for unique indexes. */ In my case, I'm seeing: distinct_real_item_keys|distinct_block_pointers| -----------------------|-----------------------| 1| 63| This is about half an hour after running VACUUM FULL ANALYZE on the table. What can I do to reduce this? [1] https://wiki.postgresql.org/wiki/Index_Maintenance#Summarize_keyspace_of_a_B-Tree_index
Em 19/09/2019 17:41, Luís Roberto Weck escreveu: > Em 19/09/2019 17:24, Luís Roberto Weck escreveu: >> Em 19/09/2019 17:11, Igor Neyman escreveu: >>> With LIMIT 1, I get 3 shared buffers hit, pretty much always. >>> >>> ____________________________________________________________________________________ >>> >>> >>> Check if assessoria_pkey index is bloated. >>> >>> Regards, >>> Igor Neyman >>> >>> >> >> With this query[1] it shows: >> >> current_database|schemaname|tblname |idxname >> |real_size|extra_size|extra_ratio|fillfactor|bloat_size|bloat_ratio|is_na| >> ----------------|----------|----------|---------------|---------|----------|-----------|----------|----------|-----------|-----| >> >> database_name |public |assessoria|assessoria_pkey| >> 16384| 0| 0.0| 90| 0.0| 0.0|false| >> >> [1]https://github.com/ioguix/pgsql-bloat-estimation/blob/master/btree/btree_bloat-superuser.sql >> >> >> > > Using the quer provided here[1] I see this comment: > > /* > * distinct_real_item_keys is how many distinct "data" fields on page > * (excludes highkey). > * > * If this is less than distinct_block_pointers on an internal page, > that > * means that there are so many duplicates in its children that > there are > * duplicate high keys in children, so the index is probably pretty > bloated. > * > * Even unique indexes can have duplicates. It's sometimes > interesting to > * watch out for how many distinct real items there are within leaf > pages, > * compared to the number of live items, or total number of items. > Ideally, > * these will all be exactly the same for unique indexes. > */ > > In my case, I'm seeing: > > distinct_real_item_keys|distinct_block_pointers| > -----------------------|-----------------------| > 1| 63| > > This is about half an hour after running VACUUM FULL ANALYZE on the > table. > > What can I do to reduce this? > > > [1] > https://wiki.postgresql.org/wiki/Index_Maintenance#Summarize_keyspace_of_a_B-Tree_inde Like Igor suggested, the index bloat seems to be at fault here. After dropping the PK, I'm getting these plans: First run (SELECT asscod, asscambol FROM ASSESSORIA WHERE asscod = 1 ORDER BY asscod): Seq Scan on public.assessoria (cost=0.00..88.01 rows=1 width=62) (actual time=0.242..0.810 rows=1 loops=1) Output: asscod, asscambol Filter: (assessoria.asscod = 1) Buffers: shared hit=88 Planning Time: 0.312 ms Execution Time: 0.876 ms (6 rows) Subsequent runs get increasingly faster, up to 0.080ms execution times. Using LIMIT 1, I get on the first run: Limit (cost=0.00..88.01 rows=1 width=62) (actual time=0.252..0.254 rows=1 loops=1) Output: asscod, asscambol Buffers: shared hit=17 -> Seq Scan on public.assessoria (cost=0.00..88.01 rows=1 width=62) (actual time=0.250..0.250 rows=1 loops=1) Output: asscod, asscambol Filter: (assessoria.asscod = 1) Buffers: shared hit=17 Planning Time: 0.334 ms Execution Time: 0.296 ms Subsequent runs look more like this: Limit (cost=0.00..88.01 rows=1 width=62) (actual time=0.057..0.057 rows=1 loops=1) Output: asscod, asscambol Buffers: shared hit=17 -> Seq Scan on public.assessoria (cost=0.00..88.01 rows=1 width=62) (actual time=0.056..0.056 rows=1 loops=1) Output: asscod, asscambol Filter: (assessoria.asscod = 1) Buffers: shared hit=17 Planning Time: 0.082 ms Execution Time: 0.068 ms I have about 6 bigint fields in this table that are very frequently updated, but none of these are indexed. I thought that by not having an index on them, would make all updates HOT, therefore not bloating the primary key index. Seems I was wrong?
I have about 6 bigint fields in this table that are very frequently
updated, but none of these are indexed. I thought that by not having an
index on them, would make all updates HOT, therefore not bloating the
primary key index. Seems I was wrong?
HOT update is only possible if there is room in the page. How wide is your single tuple?
Have you tuned autovacuum or are you running defaults? Not sure of your perception of "very frequently" updated values, but if you have bloat issue, vacuum early and often. Not sure how the math works out on a table with single tuple in terms of calculating when it is time to vacuum, but it certainly needs to be tuned differently than a table with millions of rows which is what I would be more used to.
Em 19/09/2019 19:32, Michael Lewis escreveu:
I'm not sure how to measure how wide the tuple is, can you point me in the right direction?
As fas as autovacuum options, this is what I'm using:
autovacuum_enabled=true,
fillfactor=50,
autovacuum_vacuum_threshold=25,
autovacuum_vacuum_scale_factor=0,
autovacuum_analyze_threshold=10,
autovacuum_analyze_scale_factor=0.05,
autovacuum_vacuum_cost_delay=10,
autovacuum_vacuum_cost_limit=1000,
toast.autovacuum_enabled=true
By "very frequently" I mean I can update it up to 800000 times a day. Usually this number is closer to 100000.
I have about 6 bigint fields in this table that are very frequently
updated, but none of these are indexed. I thought that by not having an
index on them, would make all updates HOT, therefore not bloating the
primary key index. Seems I was wrong?HOT update is only possible if there is room in the page. How wide is your single tuple?Have you tuned autovacuum or are you running defaults? Not sure of your perception of "very frequently" updated values, but if you have bloat issue, vacuum early and often. Not sure how the math works out on a table with single tuple in terms of calculating when it is time to vacuum, but it certainly needs to be tuned differently than a table with millions of rows which is what I would be more used to.
I'm not sure how to measure how wide the tuple is, can you point me in the right direction?
As fas as autovacuum options, this is what I'm using:
autovacuum_enabled=true,
fillfactor=50,
autovacuum_vacuum_threshold=25,
autovacuum_vacuum_scale_factor=0,
autovacuum_analyze_threshold=10,
autovacuum_analyze_scale_factor=0.05,
autovacuum_vacuum_cost_delay=10,
autovacuum_vacuum_cost_limit=1000,
toast.autovacuum_enabled=true
By "very frequently" I mean I can update it up to 800000 times a day. Usually this number is closer to 100000.
=?UTF-8?Q?Lu=c3=ads_Roberto_Weck?= <luisroberto@siscobra.com.br> writes:
> As fas as autovacuum options, this is what I'm using:
> autovacuum_vacuum_scale_factor=0,
Ugh ... maybe I'm misremembering, but I *think* that has the effect
of disabling autovac completely. You don't want zero.
Check in pg_stat_all_tables.last_autovacuum to see if anything
is happening. If the dates seem reasonably current, then I'm wrong.
regards, tom lane
Hi all, I sometimes set autovacuum_vacuum_scale factor = 0 but only when I also set autovacuum_vacuum_threshold to some non-zero number to force vacuums after a certain number of rows are updated. It takes the math out of it by setting the threshold explicitly. But in this case he has also set autovacuum_vacuum_threshold to only 25! So I think you have to fix your settings by increasing one or both accordingly. Regards, Michael Vitale Tom Lane wrote on 9/19/2019 6:57 PM: > =?UTF-8?Q?Lu=c3=ads_Roberto_Weck?= <luisroberto@siscobra.com.br> writes: >> As fas as autovacuum options, this is what I'm using: >> autovacuum_vacuum_scale_factor=0, > Ugh ... maybe I'm misremembering, but I *think* that has the effect > of disabling autovac completely. You don't want zero. > > Check in pg_stat_all_tables.last_autovacuum to see if anything > is happening. If the dates seem reasonably current, then I'm wrong. > > regards, tom lane > >