MIME-Version: 1.0
From: Vijaykumar Jain <vijaykumarjain.github@gmail.com>
Date: Wed, 16 Oct 2024 01:20:56 +0530
Message-ID: <CAM+6J96kmqmHJPWOU-Q9607BO9DiXCHAkHyuZtdF2Hc_6qVFtg@mail.gmail.com>
Subject: how to know if the sql will run a seq scan
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000029fc4a0624894661"
Archived-At: <https://www.postgresql.org/message-id/CAM%2B6J96kmqmHJPWOU-Q9607BO9DiXCHAkHyuZtdF2Hc_6qVFtg%40mail.gmail.com>
Precedence: bulk

--00000000000029fc4a0624894661
Content-Type: text/plain; charset="UTF-8"

Hi,

tl;dr
I am trying to learn what sql can result in a full seq scan.

Basically there is a lot of info on the internet of what ddl change may
take an access exclusive lock while running a seq scan and hold for long.
 And for some cases we can make use of
"not valid" constraint and then run a validate constraint as work arounds
to avoid long exclusive locks etc.
but how do we check the same. i mean for dmls there is a explain/
auto_explain.

but for DDLs, how do we check the same.
i tried to isolate my setup and use pg_stat_user_tables and monitor the
same, which helped, but it is not useful as it does not link me to what
process/command invoked the seq scan.

am i clear in my question ?

if yes,
how do i log an alter table that may or may not do a seq scan, that may or
may not rewrite the table file on disk etc.

its a useless question, i am just playing with it for building knowledge,
no requirement as such.


/*
postgres=# \d t
                 Table "public.t"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 col1   | integer |           |          |

postgres=# insert into t select 0 from generate_series(1, 1000000) x;
INSERT 0 1000000

-- this does a full seq scan as new constraint
postgres=# alter table t add constraint col1c check ( col1 < 2 );
ALTER TABLE
-- this will not since the table has valid constraint to make it think only
worry about changed data ?
postgres=# insert into t values (3);
ERROR:  new row for relation "t" violates check constraint "col1c"
DETAIL:  Failing row contains (3).

-- the below setup making use of not valid and validate constraint still
runs a seq scan but does not block writes
postgres=# alter table t add constraint col1c_not_neg check ( col1 > -1 )
not valid;                                        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 =
't';
-[ RECORD 1 ]-+------------------------------
relname       | t
seq_scan      | 7
last_seq_scan | 2024-10-15 19:34:46.837628+00
age           | -00:06:46.030264
seq_tup_read  | 4000000

postgres=# alter table t validate constraint col1c_not_neg;
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 =
't';
-[ RECORD 1 ]-+------------------------------
relname       | t
seq_scan      | 8
last_seq_scan | 2024-10-15 19:41:50.931282+00
age           | -00:00:01.85388
seq_tup_read  | 5000000

postgres=# -- now i dont want this seq scan, so i update the pg_constraint
(ok we dont do this but i want to trace seq scans)

postgres=# alter table t drop constraint col1c_not_neg;
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 =
't';
-[ RECORD 1 ]-+------------------------------
relname       | t
seq_scan      | 8
last_seq_scan | 2024-10-15 19:41:50.931282+00
age           | -00:00:21.980611
seq_tup_read  | 5000000

postgres=# alter table t add constraint col1c_not_neg check ( col1 > -1 )
not valid;                                        ALTER TABLE
postgres=# select oid from pg_constraint where conrelid =
't'::regclass::oid and convalidated = 'f';                        -[ RECORD
1 ]
oid | 16410

-- i save a seq scan in validate constraint because i know my data. (like
in attaching partitions etc) by updating the catalog directly

postgres=# update pg_constraint set convalidated = 't' where conrelid =
't'::regclass::oid and convalidated = 'f' and oid = 16410;
UPDATE 1

postgres=#  select relname,seq_scan,last_seq_scan, age(last_seq_scan,
current_timestamp), seq_tup_read from pg_stat_user_tables where relname =
't';
-[ RECORD 1 ]-+------------------------------
relname       | t
seq_scan      | 8
last_seq_scan | 2024-10-15 19:41:50.931282+00
age           | -00:05:14.066944
seq_tup_read  | 5000000

but how do i log this seq scan here for this sql.
*/

if this does not make sense,  pls ignore. not critical.
-- 
Thanks,
Vijay

Open to work
Resume - Vijaykumar Jain <https://github.com/cabecada>

--00000000000029fc4a0624894661
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"><div><b=
r></div><div>Hi,</div><div><br></div><div>tl;dr</div><div>I am trying to le=
arn what sql can result in a full seq scan.<br></div><div><br></div><div>Ba=
sically there is a lot of info on the internet of what ddl change may take =
an access exclusive lock while running=C2=A0a seq scan and hold for long.</=
div><div>=C2=A0And for some cases we can make use of=C2=A0</div><div>&quot;=
not valid&quot; constraint and then run a validate constraint as work aroun=
ds to avoid long exclusive locks etc.</div><div>but how do we check the sam=
e. i mean for dmls there is a explain/ auto_explain.</div><div><br></div><d=
iv>but for DDLs, how do we check the same.</div><div>i tried to isolate my =
setup and use pg_stat_user_tables and monitor the same, which helped, but i=
t is not useful as it does not link me to what process/command invoked the =
seq scan.</div><div><br></div><div>am i clear in my question ?</div><div><b=
r></div><div>if yes,=C2=A0</div><div>how do i log an alter table that may o=
r may not do a seq scan, that may or may not rewrite the table file on disk=
 etc.</div><div>=C2=A0=C2=A0</div><div>its a useless question, i am just pl=
aying with it for building knowledge, no requirement as such.</div><div><br=
></div><div><br></div><div>/*</div><div>postgres=3D# \d t</div><div>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Table &quot;public.t=
&quot;</div><div>=C2=A0Column |=C2=A0 Type=C2=A0 =C2=A0| Collation | Nullab=
le | Default</div><div>--------+---------+-----------+----------+---------<=
/div><div>=C2=A0col1=C2=A0 =C2=A0| integer |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |</div><div><br></div><div>po=
stgres=3D# insert into t select 0 from generate_series(1, 1000000) x;</div>=
<div>INSERT 0 1000000</div><div><br></div><div>-- this does a full seq scan=
 as new constraint=C2=A0</div><div>postgres=3D# alter table t add constrain=
t col1c check ( col1 &lt; 2 );</div><div>ALTER TABLE</div><div>-- this will=
 not since the table has valid constraint to make it think only worry about=
 changed data ?</div><div>postgres=3D# insert into t values (3);</div><div>=
ERROR:=C2=A0 new row for relation &quot;t&quot; violates check constraint &=
quot;col1c&quot;</div><div>DETAIL:=C2=A0 Failing row contains (3).</div><di=
v><br></div><div>-- the below setup making use of not valid and validate co=
nstraint still runs a seq scan but does not block writes</div><div>postgres=
=3D# alter table t add constraint col1c_not_neg check ( col1 &gt; -1 ) not =
valid;=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 ALTE=
R TABLE<br></div><div><div>postgres=3D#=C2=A0 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;t&#39;;</div><div>-[ RECORD 1 ]-+-------=
-----------------------</div><div>relname=C2=A0 =C2=A0 =C2=A0 =C2=A0| t</di=
v><div>seq_scan=C2=A0 =C2=A0 =C2=A0 | 7</div><div>last_seq_scan | 2024-10-1=
5 19:34:46.837628+00</div><div>age=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
| -00:06:46.030264</div><div>seq_tup_read=C2=A0 | 4000000</div><div><br></d=
iv><div>postgres=3D# alter table t validate constraint col1c_not_neg;</div>=
<div>ALTER TABLE</div><div>postgres=3D#=C2=A0 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;t&#39;;</div><div>-[ RECORD 1 ]-+-------=
-----------------------</div><div>relname=C2=A0 =C2=A0 =C2=A0 =C2=A0| t</di=
v><div>seq_scan=C2=A0 =C2=A0 =C2=A0 | 8</div><div>last_seq_scan | 2024-10-1=
5 19:41:50.931282+00</div><div>age=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
| -00:00:01.85388</div><div>seq_tup_read=C2=A0 | 5000000</div><div><br></di=
v><div>postgres=3D# -- now i dont want this seq scan, so i update the pg_co=
nstraint (ok we dont=C2=A0do this but i want to trace seq scans)</div><div>=
<br></div><div>postgres=3D# alter table t drop constraint col1c_not_neg;</d=
iv><div>ALTER TABLE</div><div>postgres=3D#=C2=A0 select relname,seq_scan,la=
st_seq_scan, age(last_seq_scan, current_timestamp), seq_tup_read from pg_st=
at_user_tables where relname =3D &#39;t&#39;;</div><div>-[ RECORD 1 ]-+----=
--------------------------</div><div>relname=C2=A0 =C2=A0 =C2=A0 =C2=A0| t<=
/div><div>seq_scan=C2=A0 =C2=A0 =C2=A0 | 8</div><div>last_seq_scan | 2024-1=
0-15 19:41:50.931282+00</div><div>age=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0| -00:00:21.980611</div><div>seq_tup_read=C2=A0 | 5000000</div><div><br>=
</div><div>postgres=3D# alter table t add constraint col1c_not_neg check ( =
col1 &gt; -1 ) not valid;=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 ALTER TABLE</div><div>postgres=3D# select oid from pg_con=
straint where conrelid =3D &#39;t&#39;::regclass::oid and convalidated =3D =
&#39;f&#39;;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 -[ RECORD 1 ]</div><div>oid | 16410</div><div><br></di=
v><div>-- i save a seq scan in validate constraint because i know my data. =
(like in attaching partitions etc) by updating the catalog directly</div><d=
iv><br></div><div>postgres=3D# update pg_constraint set convalidated =3D &#=
39;t&#39; where conrelid =3D &#39;t&#39;::regclass::oid and convalidated =
=3D &#39;f&#39; and oid =3D 16410;<br></div><div>UPDATE 1</div></div><div><=
br></div><div><div>postgres=3D#=C2=A0 select relname,seq_scan,last_seq_scan=
, age(last_seq_scan, current_timestamp), seq_tup_read from pg_stat_user_tab=
les where relname =3D &#39;t&#39;;</div><div>-[ RECORD 1 ]-+---------------=
---------------</div><div>relname=C2=A0 =C2=A0 =C2=A0 =C2=A0| t</div><div>s=
eq_scan=C2=A0 =C2=A0 =C2=A0 | 8</div><div>last_seq_scan | 2024-10-15 19:41:=
50.931282+00</div><div>age=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| -00:05=
:14.066944</div><div>seq_tup_read=C2=A0 | 5000000</div></div><div><br></div=
><div>but how do i log this seq scan here for this sql.</div><div>*/</div><=
div><br></div><div>if this does not make sense,=C2=A0 pls ignore. not criti=
cal.</div><span class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"=
ltr" class=3D"gmail_signature"><div dir=3D"ltr">Thanks,<div>Vijay</div><div=
><br></div><div>Open to work</div><div>Resume -=C2=A0<a href=3D"https://git=
hub.com/cabecada" target=3D"_blank">Vijaykumar Jain</a><br></div></div></di=
v></div></div></div></div>

--00000000000029fc4a0624894661--