Feedback-ID: i76984098:Fastmail
Message-ID: <d2e66f9e-ccab-4cb2-8da3-b333c6c42811@aklaver.com>
Date: Wed, 16 Oct 2024 09:40:59 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: how to know if the sql will run a seq scan
To: Vijaykumar Jain <vijaykumarjain.github@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
References: <CAM+6J96kmqmHJPWOU-Q9607BO9DiXCHAkHyuZtdF2Hc_6qVFtg@mail.gmail.com>
 <ed5e1318-b571-44ab-8a48-750159410097@aklaver.com>
 <CAM+6J96rMZJnEGvoQyuzmEw6oH6gg79A5Ahr4yKF_3RVYVM8mA@mail.gmail.com>
 <7448286f-6868-4f77-b457-5a1a943d8576@aklaver.com>
 <CAM+6J96XcVrkza-WxeLsa9C3eVT6YRN6tm9ytmYpai-F1n64Eg@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAM+6J96XcVrkza-WxeLsa9C3eVT6YRN6tm9ytmYpai-F1n64Eg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/d2e66f9e-ccab-4cb2-8da3-b333c6c42811%40aklaver.com>
Precedence: bulk

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@aklaver.com