Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t1JF0-00Fdoi-E6 for pgsql-general@arkaria.postgresql.org; Thu, 17 Oct 2024 05:40:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1t1JEx-00Gm3s-RR for pgsql-general@arkaria.postgresql.org; Thu, 17 Oct 2024 05:40:04 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t1JEx-00Gm3N-CK for pgsql-general@lists.postgresql.org; Thu, 17 Oct 2024 05:40:03 +0000 Received: from mail-ed1-x542.google.com ([2a00:1450:4864:20::542]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t1JEq-001Izm-VY for pgsql-general@postgresql.org; Thu, 17 Oct 2024 05:40:02 +0000 Received: by mail-ed1-x542.google.com with SMTP id 4fb4d7f45d1cf-5c9150f9ed4so693635a12.0 for ; Wed, 16 Oct 2024 22:39:56 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1729143595; x=1729748395; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=FZ9I6u4QSEQqe3Haa8kMH5G4T58kYQVr5Jug5dSXRKk=; b=WaK+sW5FGi8pp9Ue8y6BJ+ectKohrOn8tY3wHeEJj3nDIBfpUa70/iVfu8YFoARLoS 2/XcsZWzyF+41CeVIHwmsW5W0KO65VEdA1i+R5078EpcdRXttLoif9AHEwYhlOYvLdsk E2D3jbEcpaNsn0Y665vfmCwqu0d/JGujqbHpLRIryf9nBVWRKgsYQgrtxLuq5oxgU7QU a89n7ajM+fLNg/ult7Lk5DwyGYtfRYGxm6cU013XIZqZMNElJ0y64ZNW+ebSr5ELfOcY hAAlE5aEMBWQbRrb5wbYS12ph9rImOtZP62JKrdtmr84susyNkR95myTPE9DI4N2EMc0 gU1A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1729143595; x=1729748395; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=FZ9I6u4QSEQqe3Haa8kMH5G4T58kYQVr5Jug5dSXRKk=; b=bc+Cf3Nuvj2nPeCvtY5sm7RBsOjOpMfBB32Q9pzA9u3pKT6RdFi4x2meGfABVHE2qo 6kMwcuNt9fmyfNUhgAYfFS4NfCXngbzMEvWa7+hwtcFIkP6y+j6KOrIIdcU5QfDXvavI ZWOvMpMldn5ppEcTRKavo1qDN7JCH1FY9nhLcstowma6F2in/VcSUXn+3wY2MIulQy6f /Pv2+m603FnM3KxB7swKuE0Veq8KHxfGlpuJSrzpMl1hJE5CIkawaI4k+cJ/EIbXBAKw jwURq5APWDLJl3aiw4y6ZerD0X8tsQ4R8hnCRs+dqMP8P24rJrnl+2KVV+/kyuCiND5S R5Ag== X-Gm-Message-State: AOJu0YwqgRcSe+XskYNsRd6jIRSYuVSt2rbat5RaaOW1Lko8j9lJuOEi p+ayNjby+ue4QsUURstm/7/cZfvyuxfZ+kmwabl6zLkukrv1K1V+hmKXSpVl01x+yCvqM3klDsp qEUvmz7T1Z3YM/dLaTzh9uEJJiqsp+8yJH/Y= X-Google-Smtp-Source: AGHT+IFWzxxiUIgWLQBPMyTDfLSD5lfS9vts2gYmlHVuCXdxX99L+m3NHjmyGfpyY0jr9xdC8kVgnzBOR5js+RhEp1w= X-Received: by 2002:a17:907:9801:b0:a9a:2afc:e4e4 with SMTP id a640c23a62f3a-a9a34f11334mr513703366b.59.1729143594966; Wed, 16 Oct 2024 22:39:54 -0700 (PDT) MIME-Version: 1.0 References: <7448286f-6868-4f77-b457-5a1a943d8576@aklaver.com> In-Reply-To: From: Vijaykumar Jain Date: Thu, 17 Oct 2024 11:09:43 +0530 Message-ID: Subject: Re: how to know if the sql will run a seq scan To: Adrian Klaver Cc: pgsql-general Content-Type: multipart/alternative; boundary="000000000000be67b30624a59dc2" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000be67b30624a59dc2 Content-Type: text/plain; charset="UTF-8" > > > > 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. > Thanks Adrian, I tried similar things as in the doc, but I found my mistake. I casually did not put a constraint of not null on the partition column. as a result i think to invalidate any of that data, it does a scan despite the boundary constraint. which when i used not null for the partition column, the scans stopped. although my question was asking something else, but maybe i will not be able to put it clearly. at least this part got addressed well. thanks for your help and time. /* postgres=# drop table t; DROP TABLE postgres=# drop table t1; ERROR: table "t1" does not exist postgres=# create table t(col1 int) partition by list(col1); CREATE TABLE postgres=# create table t1 (LIKE t INCLUDING DEFAULTS INCLUDING CONSTRAINTS); CREATE TABLE postgres=# insert into t1 select 0 from generate_series(1, 100000) x; INSERT 0 100000 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-17 05:33:40.080319+00 | -00:00:02.488752 | 100000 (1 row) postgres=# alter table t attach partition t1 for values in (0); -- note the scan count increased as col1 was not set to not null 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-17 05:33:48.248001+00 | -00:00:01.510694 | 200000 (1 row) postgres=# drop table t; DROP TABLE postgres=# drop table t1; ERROR: table "t1" does not exist postgres=# create table t(col1 int not null) partition by list(col1); CREATE TABLE postgres=# create table t1 (LIKE t INCLUDING DEFAULTS INCLUDING CONSTRAINTS); CREATE TABLE postgres=# insert into t1 select 0 from generate_series(1, 100000) x; INSERT 0 100000 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-17 05:34:41.363401+00 | -00:00:03.19836 | 100000 (1 row) postgres=# alter table t attach partition t1 for values in (0); -- note no scan count bump as not null on column to avoid scanning to filter those values - 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-17 05:34:41.363401+00 | -00:00:08.241812 | 100000 (1 row) */ --000000000000be67b30624a59dc2 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


I don't have time now to create an example, but I can point you at:

https://ww= w.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLAR= ATIVE

5.12.2.2. Partition Maintenance

"As an alternative to creating a new partition, it is sometimes more <= br> convenient to create a new table separate from the partition structure
and attach it as a partition later. ... "

Read the section starting above.

Thanks= Adrian,
I tried similar things as in the doc, but I found my mis= take.
I casually did not put a constraint of not null on the part= ition column. as a result i think to invalidate any of that data, it does a= scan despite the boundary constraint.

which when = i used not null for the partition column, the scans stopped.
alth= ough my question was asking something else, but maybe i will not be able to= put it clearly.
at least this part got addressed well. thanks fo= r your help and time.



/*
postgres=3D# drop table t;
DROP TABLE
po= stgres=3D# drop table t1;
ERROR:=C2=A0 table "t1" does = not exist
postgres=3D# create table t(col1 int) partition by list= (col1);=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= =A0 =C2=A0 CREATE TABLE
postgres=3D# create table t1 (LIKE t INCL= UDING DEFAULTS INCLUDING CONSTRAINTS);=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=A0CREATE TABLE
postgres=3D# insert into t1 select 0 from generate_series(1, 100000)= x;
INSERT 0 100000
postgres=3D# alter table t1 add con= straint col10 check (col1 =3D 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 ALTER TABLE
postgres=3D# select relname,seq_= scan,last_seq_scan, age(last_seq_scan, current_timestamp), seq_tup_read fro= m pg_stat_user_tables where relname =3D 't1';
=C2=A0relna= me | 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
---------+----------+----------------------= ---------+------------------+--------------
=C2=A0t1=C2=A0 =C2=A0= =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 1 | 2024-10-17 05:33:40.080319+00 | -0= 0:00:02.488752 |=C2=A0 =C2=A0 =C2=A0 =C2=A0100000
(1 row)

postgres=3D# alter table t attach partition t1 for values= in (0);=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -- note the scan count increased= as col1 was not set to not null
=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
postgres=3D# 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 't1';
=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_rea= d
---------+----------+-------------------------------+----------= --------+--------------
=C2=A0t1=C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2= =A0 =C2=A0 =C2=A0 2 | 2024-10-17 05:33:48.248001+00 | -00:00:01.510694 |=C2= =A0 =C2=A0 =C2=A0 =C2=A0200000
(1 row)

p= ostgres=3D# drop table t;
DROP TABLE
postgres=3D# drop = table t1;
ERROR:=C2=A0 table "t1" does not exist
<= div>postgres=3D# create table t(col1 int not null) partition by list(col1);= =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=A0CREATE TABLE
post= gres=3D# create table t1 (LIKE t INCLUDING DEFAULTS INCLUDING CONSTRAINTS);= =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=A0CREATE TABLE
postgres=3D# insert into t1 sele= ct 0 from generate_series(1, 100000) x;=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=A0INSERT 0 100000
postgres=3D# alter table t1 add cons= traint col10 check (col1 =3D 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=A0
ALTER TABLE
postgres=3D# sel= ect relname,seq_scan,last_seq_scan, age(last_seq_scan, current_timestamp), = seq_tup_read from pg_stat_user_tables where relname =3D 't1';
=
=C2=A0relname | seq_scan |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0last_seq_s= can=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
---------+----------+---------= ----------------------+-----------------+--------------
=C2=A0t1= =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 1 | 2024-10-17 05:34:41.3= 63401+00 | -00:00:03.19836 |=C2=A0 =C2=A0 =C2=A0 =C2=A0100000
(1 = row)

postgres=3D# alter table t attach partition t= 1 for values in (0);=C2=A0 -- note no scan count bump as not null on column= to avoid scanning to filter those values=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
postgres=3D# select relname,seq_scan,last_s= eq_scan, age(last_seq_scan, current_timestamp), seq_tup_read from pg_stat_u= ser_tables where relname =3D 't1';
=C2=A0relname | seq_sc= an |=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
---------+----------+-------------------------------+---= ---------------+--------------
=C2=A0t1=C2=A0 =C2=A0 =C2=A0 |=C2= =A0 =C2=A0 =C2=A0 =C2=A0 1 | 2024-10-17 05:34:41.363401+00 | -00:00:08.2418= 12 |=C2=A0 =C2=A0 =C2=A0 =C2=A0100000
(1 row)

*/
=C2=A0
--000000000000be67b30624a59dc2--