Re: how to know if the sql will run a seq scan

From: Vijaykumar Jain <vijaykumarjain(dot)github(at)gmail(dot)com>
To: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: how to know if the sql will run a seq scan
Date: 2024-10-16 07:02:18
Message-ID: CAM+6J96XcVrkza-WxeLsa9C3eVT6YRN6tm9ytmYpai-F1n64Eg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Wed, 16 Oct 2024 at 02:59, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 10/15/24 13:50, Vijaykumar Jain wrote:
> > Sorry top posting, coz Gmail app on phone.
> >
> > Yeah, my point was for example we have a large table and we are
> > attaching a table as a partition. Now it will scan the whole table to
> > validate the constraint and that will create all sorts of problems.
>
> Now you have changed the problem description.
>
> To get a proper answer you will need to provide a more detailed
> description of what you are doing with the following information:
>
> 1) Postgres version.
>
> 2) Definition of 'large'.
>
> 3) The command/process being used to create the partition.
>
> 4) The actual constraint definition.
>
> 5) The table definition.
>
>
/*
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

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>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message mbork 2024-10-16 09:35:46 What are best practices wrt passwords?
Previous Message Laurenz Albe 2024-10-16 04:23:38 Re: serializable master and non-serializable hot standby: feasible set up?