On 10/16/24 00:02, Vijaykumar Jain wrote:
postgres=# create table t(col1 int) partition by list(col1);
CREATE TABLE
postgres=# create table t1(col1 int)
postgres-# ;
CREATE TABLE
postgres=# insert into t1 select 0 from generate_series(1, 100000) x;
INSERT 0 100000
postgres=# select relname,seq_scan,last_seq_scan, age(last_seq_scan,
current_timestamp), seq_tup_read from pg_stat_user_tables where relname
= 't1';
relname | seq_scan | last_seq_scan | age | seq_tup_read
---------+----------+---------------+-----+--------------
t1 | 0 | | | 0
(1 row)
postgres=# alter table t1 add constraint col10 check (col1 = 0);
ALTER TABLE
postgres=# select relname,seq_scan,last_seq_scan, age(last_seq_scan,
current_timestamp), seq_tup_read from pg_stat_user_tables where relname
= 't1';
relname | seq_scan | last_seq_scan | age
| seq_tup_read
---------+----------+-------------------------------+------------------+--------------
t1 | 1 | 2024-10-16 06:46:28.641281+00 | -00:00:03.258432
| 100000
(1 row)
postgres=# -- this results in a seq scan , which is ok, but then when i
attach the partition it does a seq scan again
postgres=# alter table t attach partition t1 for values in (0);
ALTER TABLE
postgres=# select relname,seq_scan,last_seq_scan, age(last_seq_scan,
current_timestamp), seq_tup_read from pg_stat_user_tables where relname
= 't1';
relname | seq_scan | last_seq_scan | age
| seq_tup_read
---------+----------+-------------------------------+------------------+--------------
t1 | 2 | 2024-10-16 06:46:59.512201+00 | -00:00:02.498771
| 200000
(1 row)
postgres=# -- why , when there is a constraint that helps with the
partition boundary/value
postgres=# alter table t detach partition t1;
ALTER TABLE
postgres=# alter table t attach partition t1 for values in (0);
ALTER TABLE
postgres=# select relname,seq_scan,last_seq_scan, age(last_seq_scan,
current_timestamp), seq_tup_read from pg_stat_user_tables where relname
= 't1';
relname | seq_scan | last_seq_scan | age
| seq_tup_read
---------+----------+-------------------------------+------------------+--------------
t1 | 3 | 2024-10-16 06:54:28.780145+00 | -00:00:03.358524
| 300000
(1 row)
-- despite there being a constraint, it does a full table scan to attach
the partition. why ? note the tup read is full table of t1.
*/
above is one of the cases i found.
my core question still was, how do i know which statement will cause a
full table rewrite
full table scan
I don't have time now to create an example, but I can point you at:
https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE
5.12.2.2. Partition Maintenance
"As an alternative to creating a new partition, it is sometimes more
convenient to create a new table separate from the partition structure
and attach it as a partition later. ... "
Read the section starting above.
how do i get to know that. i know implictly i can use the above stat
tables and pg_rel_filepath function etc to figure out the change in oid
, update in seq count etc.
but i want to pin point which statement made what change among 100 other
statements in production.
I mean is there a way that a certain alter table will do a table rewrite
on disk and other alter table will not.
access exclusive lock on tables does not help answer that question.
if i am not clear, maybe ignore my question. i have some issues
explaining things clearly, so i try to use demos.
Thanks,
Vijay
Open to work
Resume - Vijaykumar Jain <https://github.com/cabecada>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
