public inbox for [email protected]  

help / color / mirror / Atom feed

From: Vijaykumar Jain <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: how to know if the sql will run a seq scan
Date: Thu, 17 Oct 2024 11:09:43 +0530
Message-ID: <CAM+6J94=uuuYjpvRByBhGLtJNNHjC68e_v+Kf8haGviqmNCm3A@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAM+6J96kmqmHJPWOU-Q9607BO9DiXCHAkHyuZtdF2Hc_6qVFtg@mail.gmail.com>
	<[email protected]>
	<CAM+6J96rMZJnEGvoQyuzmEw6oH6gg79A5Ahr4yKF_3RVYVM8mA@mail.gmail.com>
	<[email protected]>
	<CAM+6J96XcVrkza-WxeLsa9C3eVT6YRN6tm9ytmYpai-F1n64Eg@mail.gmail.com>
	<[email protected]>

>
>
>
> 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)

*/

view thread (4+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: how to know if the sql will run a seq scan
  In-Reply-To: <CAM+6J94=uuuYjpvRByBhGLtJNNHjC68e_v+Kf8haGviqmNCm3A@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox