MIME-Version: 1.0
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>
In-Reply-To: <7448286f-6868-4f77-b457-5a1a943d8576@aklaver.com>
From: Vijaykumar Jain <vijaykumarjain.github@gmail.com>
Date: Wed, 16 Oct 2024 12:32:18 +0530
Message-ID: <CAM+6J96XcVrkza-WxeLsa9C3eVT6YRN6tm9ytmYpai-F1n64Eg@mail.gmail.com>
Subject: Re: how to know if the sql will run a seq scan
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000566cd5062492a731"
Archived-At: <https://www.postgresql.org/message-id/CAM%2B6J96XcVrkza-WxeLsa9C3eVT6YRN6tm9ytmYpai-F1n64Eg%40mail.gmail.com>
Precedence: bulk

--000000000000566cd5062492a731
Content-Type: text/plain; charset="UTF-8"

On Wed, 16 Oct 2024 at 02:59, Adrian Klaver <adrian.klaver@aklaver.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>

--000000000000566cd5062492a731
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><br></d=
iv><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On =
Wed, 16 Oct 2024 at 02:59, Adrian Klaver &lt;<a href=3D"mailto:adrian.klave=
r@aklaver.com">adrian.klaver@aklaver.com</a>&gt; wrote:<br></div><blockquot=
e class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px s=
olid rgb(204,204,204);padding-left:1ex">On 10/15/24 13:50, Vijaykumar Jain =
wrote:<br>
&gt; Sorry top posting, coz Gmail app on phone.<br>
&gt; <br>
&gt; Yeah, my point was for example we have a large table and we are <br>
&gt; attaching a table as a partition. Now it will scan the whole table to =
<br>
&gt; validate the constraint and that will create all sorts of problems.<br=
>
<br>
Now you have changed the problem description.<br>
<br>
To get a proper answer you will need to provide a more detailed <br>
description of what you are doing with the following information:<br>
<br>
1) Postgres version.<br>
<br>
2) Definition of &#39;large&#39;.<br>
<br>
3) The command/process being used to create the partition.<br>
<br>
4) The actual constraint definition.<br>
<br>
5) The table definition.<br>
<br></blockquote><div><br></div><div>/*</div><div><div>postgres=3D# create =
table t(col1 int) partition by list(col1);</div><div>CREATE TABLE</div><div=
>postgres=3D# create table t1(col1 int)</div><div>postgres-# ;</div><div>CR=
EATE TABLE</div><div>postgres=3D# insert into t1 select 0 from generate_ser=
ies(1, 100000) x;</div><div>INSERT 0 100000</div><div>postgres=3D# select r=
elname,seq_scan,last_seq_scan, age(last_seq_scan, current_timestamp), seq_t=
up_read from pg_stat_user_tables where relname =3D &#39;t1&#39;;</div><div>=
=C2=A0relname | seq_scan | last_seq_scan | age | seq_tup_read</div><div>---=
------+----------+---------------+-----+--------------</div><div>=C2=A0t1=
=C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 0 |=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 0</div><div>(1 row)</div><div><br></div><div>postg=
res=3D# alter table t1 add constraint col10 check (col1 =3D 0);</div><div>A=
LTER TABLE</div><div>postgres=3D# select relname,seq_scan,last_seq_scan, ag=
e(last_seq_scan, current_timestamp), seq_tup_read from pg_stat_user_tables =
where relname =3D &#39;t1&#39;;</div><div>=C2=A0relname | seq_scan |=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0last_seq_scan=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=
=C2=A0 =C2=A0 =C2=A0 =C2=A0age=C2=A0 =C2=A0 =C2=A0 =C2=A0 | seq_tup_read</d=
iv><div>---------+----------+-------------------------------+--------------=
----+--------------</div><div>=C2=A0t1=C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 1 | 2024-10-16 06:46:28.641281+00 | -00:00:03.258432 |=C2=A0 =
=C2=A0 =C2=A0 =C2=A0100000</div><div>(1 row)</div><div><br></div><div>postg=
res=3D# -- this results in a seq scan , which is ok, but then when i attach=
 the partition it does a seq scan again</div><div>postgres=3D# alter table =
t attach partition t1 for values in (0);=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0ALTER TABLE</div><div>postgres=3D# select=
 relname,seq_scan,last_seq_scan, age(last_seq_scan, current_timestamp), seq=
_tup_read from pg_stat_user_tables where relname =3D &#39;t1&#39;;</div><di=
v>=C2=A0relname | seq_scan |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0last_seq_scan=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0age=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 | seq_tup_read</div><div>---------+----------+-----------=
--------------------+------------------+--------------</div><div>=C2=A0t1=
=C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 2 | 2024-10-16 06:46:59.5=
12201+00 | -00:00:02.498771 |=C2=A0 =C2=A0 =C2=A0 =C2=A0200000</div><div>(1=
 row)</div><div><br></div><div>postgres=3D# -- why , when there is a constr=
aint that helps with the partition boundary/value</div><div><br></div><div>=
postgres=3D# alter table t detach partition t1;</div><div>ALTER TABLE</div>=
<div><br></div><div><div>postgres=3D# alter table t attach partition t1 for=
 values in (0);</div><div>ALTER TABLE</div><div>postgres=3D# select relname=
,seq_scan,last_seq_scan, age(last_seq_scan, current_timestamp), seq_tup_rea=
d from pg_stat_user_tables where relname =3D &#39;t1&#39;;</div><div>=C2=A0=
relname | seq_scan |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0last_seq_scan=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0age=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 | seq_tup_read</div><div>---------+----------+------------------=
-------------+------------------+--------------</div><div>=C2=A0t1=C2=A0 =
=C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 3 | 2024-10-16 06:54:28.780145+0=
0 | -00:00:03.358524 |=C2=A0 =C2=A0 =C2=A0 =C2=A0300000</div><div>(1 row)</=
div></div><div><br></div><div>-- 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.</div></div><div><br></div><div>*/</div><div><br></div><div>abo=
ve is one of the cases i found.</div><div>my core question still was, how d=
o i know which statement will cause a=C2=A0</div><div>full table rewrite</d=
iv><div>full table scan</div><div><br></div><div>how do i get to know that.=
=C2=A0i know implictly=C2=A0i can use the above stat tables and pg_rel_file=
path function etc to figure out the change in oid , update in seq count etc=
.</div><div>but i want to pin point=C2=A0which statement made what change a=
mong 100 other statements in production.</div><div><br></div><div>I mean is=
 there a way that a certain alter table will do a table rewrite on disk and=
 other alter table will not.</div><div>access exclusive lock on tables does=
 not help answer that question.=C2=A0</div><div><br></div><div>if i am not =
clear, maybe ignore my question. i have some issues explaining things clear=
ly, so i try to use demos.</div><div><br></div><div><br></div><div><br></di=
v><div><br></div><div><br></div><div><br></div><div>=C2=A0<br></div></div><=
div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Thanks,<div>Vija=
y</div><div><br></div><div>Open to work</div><div>Resume -=C2=A0<a href=3D"=
https://github.com/cabecada" target=3D"_blank">Vijaykumar Jain</a><br></div=
></div></div></div></div></div>

--000000000000566cd5062492a731--