\d don't print all the tables
Hi community, here's my shell: postgres=# \d Did not find any relations. postgres=# create schema namespace_a; CREATE SCHEMA postgres=# create schema namespace_b; CREATE SCHEMA postgres=# create table simple (name varchar); CREATE TABLE postgres=# create table namespace_a.simple (name varchar); CREATE TABLE postgres=# create table namespace_b.simple (name varchar); CREATE TABLE postgres=# \d List of relations Schema | Name | Type | Owner ++---+-- public | simple | table | postgres (1 row) postgres=# I think it should print the table on namespace_a & namespace_b, not just public, it really confused me. Can someone give me some advice? Yours, Wen Yi.
[Beginner Question]Is there way to test the postgres's kernel function?
Hi community, I am learning the kernel of the postgres, and I want to test the make_relative_path function to know what it's operational principle.(I can read it's code, and I want to actually run it) But I really don't know how to do it, wrtite a extension? Can someone give me some advice? Thanks in advance! Yours, Wen Yi.
Re: \d don't print all the tables
Hi, try: \d *.* where: *.* = schema.table Best regards / Cordialement / S pozdravem / S poštovanjem, *Antonio Čale* On Mon, Jul 24, 2023 at 11:43 AM Wen Yi wrote: > Hi community, > here's my shell: > > postgres=# \d > Did not find any relations. > postgres=# create schema namespace_a; > CREATE SCHEMA > postgres=# create schema namespace_b; > CREATE SCHEMA > postgres=# create table simple (name varchar); > CREATE TABLE > postgres=# create table namespace_a.simple (name varchar); > CREATE TABLE > postgres=# create table namespace_b.simple (name varchar); > CREATE TABLE > postgres=# \d > List of relations > Schema | Name | Type | Owner > ++---+-- > public | simple | table | postgres > (1 row) > > postgres=# > > I think it should print the table on namespace_a & namespace_b, not just > public, it really confused me. > Can someone give me some advice? > > Yours, > Wen Yi. >
Re: How to improve the performance of my SQL query？
>Did you change any parameters that have an impact on query planning? >You can see that in the output of EXPLAIN (ANALYZE, BUFFERS, SETTINGS). I added some parameters and re-executed the Execution Plan. Except for the index not taking effect, I still don't know the reason why the index is not working. Is it because there is too much data that meets the conditions? EXPLAIN (ANALYZE ON, VERBOSE ON, COSTS ON, BUFFERS ON) select COUNT(ET_CD) from TBL_SHA WHERE TBL_SHA.MS_CD = 'MLD009' and TBL_SHA.ETRYS in (select TBL_INF.RY_CD from TBL_INF WHERE TBL_INF.MS_CD = 'MLD009' AND TBL_INF.RY_CD = '0001' ) - Execution Plan - Limit (cost=2728633.22..2728633.23 rows=1 width=8) (actual time=128691.521..128717.677 rows=1 loops=1) Output: (count(tbl_sha.et_cd)) Buffers: shared hit=58948 read=2112758 I/O Timings: read=357249.120 -> Aggregate (cost=2728633.22..2728633.23 rows=1 width=8) (actual time=128691.519..128717.674 rows=1 loops=1) Output: count(tbl_sha.et_cd) Buffers: shared hit=58948 read=2112758 I/O Timings: read=357249.120 -> Nested Loop (cost=1000.29..2722556.76 rows=2430587 width=9) (actual time=2.364..128350.279 rows=2613500 loops=1) Output: tbl_sha.et_cd Buffers: shared hit=58948 read=2112758 I/O Timings: read=357249.120 -> Index Only Scan using tbl_inf_pkc on mtpdb.tbl_inf (cost=0.29..8.31 rows=1 width=9) (actual time=0.046..0.051 rows=1 loops=1) Output: tbl_inf.ms_cd, tbl_inf.ry_cd Index Cond: ((tbl_inf.ms_cd = 'MLD009'::bpchar) AND (tbl_inf.ry_cd = '0001'::bpchar)) Heap Fetches: 1 Buffers: shared hit=4 -> Gather (cost=1000.00..2698242.58 rows=2430587 width=18) (actual time=2.315..127773.087 rows=2613500 loops=1) Output: tbl_sha.et_cd, tbl_sha.etrys Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=58944 read=2112758 I/O Timings: read=357249.120 -> Parallel Seq Scan on mtpdb.tbl_sha (cost=0.00..2454183.88 rows=1012745 width=18) (actual time=952.728..127583.089 rows=871167 loops=3) Output: tbl_sha.et_cd, tbl_sha.etrys Filter: ((tbl_sha.ms_cd = 'MLD009'::bpchar) AND (tbl_sha.etrys = '0001'::bpchar)) Rows Removed by Filter: 14062278 Buffers: shared hit=58944 read=2112758 I/O Timings: read=357249.120 Worker 0: actual time=1432.292..127762.181 rows=988036 loops=1 Buffers: shared hit=17875 read=706862 I/O Timings: read=119193.744 Worker 1: actual time=1425.878..127786.777 rows=992381 loops=1 Buffers: shared hit=19813 read=706359 I/O Timings: read=119386.899 Planning: Buffers: shared hit=42 Planning Time: 1.024 ms Execution Time: 128717.731 ms At 2023-07-24 13:43:46, "Laurenz Albe" wrote: >On Fri, 2023-07-21 at 09:43 +0800, gzh wrote: >> The definitions of the columns used in SQL are as follows. >> >> TBL_SHA >> >> ms_cd character(6) NOT NULL -- PRIMARY KEY >> et_cd character(8) >> etrys character(8) >> >> TBL_INF >> >> ms_cd character(6) NOT NULL -- PRIMARY KEY >> ry_cd character(8) NOT NULL -- PRIMARY KEY >> >> I made some modifications to the data, and I realized that I should not >> change the length of the data. >> The actual data and its corresponding execution plan are shown below. >> >> explain analyze >> select COUNT(ET_CD) >> from TBL_SHA >> WHERE TBL_SHA.MS_CD = 'MLD009' >> and TBL_SHA.ETRYS in >>(select TBL_INF.RY_CD >> from TBL_INF >> WHERE TBL_INF.MS_CD = 'MLD009' >>AND TBL_INF.RY_CD = '0001' >>) >> - Execution Plan - >> Limit (cost=2738709.57..2738709.58 rows=1 width=8) (actual >> time=124168.769..124168.771 rows=1 loops=1) >> -> Aggregate (cost=2738709.57..2738709.58 rows=1 width=8) (actual >> time=124168.767..124168.769 rows=1 loops=1) >> -> Nested Loop (cost=0.29..2730702.63 rows=3202774 width=9) >> (actual time=97264.166..123920.769 rows=320 loops=1) >> -> Index Only Scan using TBL_INF_pkc on TBL_INF >> (cost=0.29..8.31 rows=1 width=9) (actual time=0.025..0.030 rows=1 loops=1) >> Index Cond: ((MS_CD = 'MLD009'::bpchar) AND (RY_CD = >> '0001'::bpchar)) >> Heap Fetches: 1 >> -> Seq Scan on TBL_SHA (cost=0.00..2698666.58 rows=3202774 >> width=18) (actual time=97264.138..123554.792 rows=320 loops=1) >> Filter: ((MS_CD = 'MLD009'::bpchar) AND (ETRYS = >> '0001'::bpchar))
Re: aclitem binary encoding
On Fri, Jul 21, 2023 at 4:58 PM Tom Lane wrote:
> Joseph Koshakow writes:
> > Is this an intentional decision to not support a binary encoding for
> > aclitem types? Or is it just a lack of a feature?
>
I'm also using binary input/output, and for ACLs, when not using the usual
ACL related functions,
I parse them myself, using code inspired from the official sources.
Something like below (for DBs in this case).
Assumes you can deal with text arrays in your code of course. I hope that
helps. --DD
select ..., coalesce(datacl, acldefault('d', datdba))::text[] as acls,
from pg_database
// See getid() from
https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/acl.c
std::string_view parseAclName(std::string_view sv, std::string& name) { ...
}
// See aclparse() from
https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/acl.c
// And also https://www.postgresql.org/docs/current/ddl-priv.html
std::string_view parseAclPrivs(std::string_view sv, AclFlags& privs,
AclFlags& grantable) { ... }
// Of the form: grantee=privs/grantor
// Where empty grantee means "public"
void parseAclItem(const std::string& s, AclItem& acl) { ... }
std::string_view sv(s);
...
sv = parseAclName(sv, acl.grantee_);
...
sv = parseAclPrivs(sv, acl.privs_, acl.grantable_);
...
sv = parseAclName(sv, acl.grantor_);
...
}
void parseAcls(const std::vector& acls_in,
std::vector& acls_out) {
...
for (const std::string& acl : acls_in) {
parseAclItem(acl, acls_out.emplace_back());
}
}
Grant all privileges to user on a database
Hi,
I am running postgresql15-server 15.3 on Red Hat Enterprise Linux
release 8.7 (Ootpa)
# rpm -qa | grep -i post
postgresql15-server-15.3-2PGDG.rhel8.x86_64
postgresql15-libs-15.3-2PGDG.rhel8.x86_64
postgresql15-15.3-2PGDG.rhel8.x86_64
#
$psql
psql (15.3)
Type "help" for help.
postgres=# GRANT ALL PRIVILEGES ON DATABASE cbdevdb TO cbdevdbadmin;
postgres=# \du
List of roles
Role name | Attributes
| Member of
--++---
cbdevdbadmin || {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=#
Am I missing something? Please suggest. Thanks in advance.
Best Regards,
Kaushal
Re: \d don't print all the tables
On 7/23/23 21:25, Wen Yi wrote: Hi community, here's my shell: postgres=# \d Did not find any relations. postgres=# create schema namespace_a; CREATE SCHEMA postgres=# create schema namespace_b; CREATE SCHEMA postgres=# create table simple (name varchar); CREATE TABLE postgres=# create table namespace_a.simple (name varchar); CREATE TABLE postgres=# create table namespace_b.simple (name varchar); CREATE TABLE postgres=# \d List of relations Schema | Name | Type | Owner ++---+-- public | simple | table | postgres (1 row) postgres=# I think it should print the table on namespace_a & namespace_b, not just public, it really confused me. Can someone give me some advice? Add namespace_a and namespace_b to your search_path. Then it will work. Off the top of my head: SET search_path = namespace_a, namespace_b, public; -- Born in Arizona, moved to Babylonia.
Re: Grant all privileges to user on a database
On 7/24/23 08:15, Kaushal Shriyan wrote:
Hi,
I am running postgresql15-server 15.3 on Red Hat Enterprise Linux
release 8.7 (Ootpa)
# rpm -qa | grep -i post
postgresql15-server-15.3-2PGDG.rhel8.x86_64
postgresql15-libs-15.3-2PGDG.rhel8.x86_64
postgresql15-15.3-2PGDG.rhel8.x86_64
#
$psql
psql (15.3)
Type "help" for help.
postgres=# GRANT ALL PRIVILEGES ON DATABASE cbdevdb TO cbdevdbadmin;
postgres=# \du
List of roles
Role name | Attributes
| Member of
--++---
cbdevdbadmin || {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=#
Am I missing something? Please suggest. Thanks in advance.
No, you aren't. Role attributes are not the same as table privileges.
--
Born in Arizona, moved to Babylonia.
Re: Grant all privileges to user on a database
On Mon, Jul 24, 2023 at 6:51 PM Ron wrote:
>
> On 7/24/23 08:15, Kaushal Shriyan wrote:
> > Hi,
> >
> > I am running postgresql15-server 15.3 on Red Hat Enterprise Linux
> > release 8.7 (Ootpa)
> >
> > # rpm -qa | grep -i post
> > postgresql15-server-15.3-2PGDG.rhel8.x86_64
> > postgresql15-libs-15.3-2PGDG.rhel8.x86_64
> > postgresql15-15.3-2PGDG.rhel8.x86_64
> > #
> >
> > $psql
> > psql (15.3)
> > Type "help" for help.
> >
> > postgres=# GRANT ALL PRIVILEGES ON DATABASE cbdevdb TO cbdevdbadmin;
> > postgres=# \du
> > List of roles
> >Role name | Attributes
> > | Member of
> >
--++---
> > cbdevdbadmin |
| {}
> > postgres | Superuser, Create role, Create DB, Replication, Bypass
RLS | {}
> >
> > postgres=#
> >
> > Am I missing something? Please suggest. Thanks in advance.
>
> No, you aren't. Role attributes are not the same as table privileges.
>
>
> --
> Born in Arizona, moved to Babylonia.
>
>
Thanks Ron for the quick response and appreciate it. I am currently facing
the below error while configuring the PostgreSQL 15.3 from Drupal CMS
version 9.5.10 (https://www.drupal.org/project/drupal/) web interface.
Failed to CREATE a test table on your database server with the command
CREATE TABLE {drupal_install_test} (id int NOT NULL PRIMARY KEY). The
server reports the following message: SQLSTATE[42501]: Insufficient
privilege: 7 ERROR: permission denied for schema public LINE 1: CREATE
TABLE "drupal_install_test" (id int NOT NULL PRIMARY ... ^: CREATE TABLE
"drupal_install_test" (id int NOT NULL PRIMARY KEY); Array ( ) .
Are you sure the configured username has the necessary permissions to
create tables in the database?
Please suggest. Thanks in advance.
Best Regards,
Kaushal
Re: \d don't print all the tables
On 7/23/23 19:25, Wen Yi wrote: Hi community, here's my shell: I think it should print the table on namespace_a & namespace_b, not just public, it really confused me. Can someone give me some advice? Read about search_path: https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH Yours, Wen Yi. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Grant all privileges to user on a database
On Monday, July 24, 2023, Kaushal Shriyan wrote: > > > Are you sure the configured username has the necessary permissions to > create tables in the database? > The error message is misleading, you can’t directly create tables in a database, you must create them in a schema and the permission to do so is granted to the role on the schema, not the database. David J.
Re: Grant all privileges to user on a database
"David G. Johnston" writes: > The error message is misleading, you can’t directly create tables in a > database, you must create them in a schema and the permission to do so is > granted to the role on the schema, not the database. The actual server message is going to be just ERROR: permission denied for schema public I'm not sure where the "Are you sure ..." bit came from, but it wasn't Postgres. Anyway, backing up two steps, it looks like drupal hasn't yet adapted to the v15 change that removed default create privileges on the public schema (for security reasons). You might see if a newer drupal release is available. If not, the best bet would likely be GRANT ALL ON SCHEMA public TO public; If you run into more problems, a more drastic answer might be to downgrade to whatever drupal considers a supported release of Postgres. regards, tom lane
Re: \d don't print all the tables
On 2023-Jul-24, Ron wrote: > Add namespace_a and namespace_b to your search_path. Then it will work. > > Off the top of my head: > SET search_path = namespace_a, namespace_b, public; Actually it won't, because the table in the earliest schema "shadows" any other tables of the same name in later schemas, so they won't show in \d either. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ "XML!" Exclaimed C++. "What are you doing here? You're not a programming language." "Tell that to the people who use me," said XML. https://burningbird.net/the-parable-of-the-languages/
Re: How to improve the performance of my SQL query？
On Mon, Jul 24, 2023 at 5:54 PM gzh wrote:
>
> >Did you change any parameters that have an impact on query planning?
>
> >You can see that in the output of EXPLAIN (ANALYZE, BUFFERS, SETTINGS).
>
> I added some parameters and re-executed the Execution Plan.
>
> Except for the index not taking effect, I still don't know the reason why the
> index is not working.
>
> Is it because there is too much data that meets the conditions?
>
>
> EXPLAIN (ANALYZE ON, VERBOSE ON, COSTS ON, BUFFERS ON)
>
> select COUNT(ET_CD)
>
> from TBL_SHA
>
> WHERE TBL_SHA.MS_CD = 'MLD009'
>
> and TBL_SHA.ETRYS in
>
>(select TBL_INF.RY_CD
>
> from TBL_INF
>
> WHERE TBL_INF.MS_CD = 'MLD009'
>
>AND TBL_INF.RY_CD = '0001'
>
>)
>
>
> - Execution Plan -
>
> Limit (cost=2728633.22..2728633.23 rows=1 width=8) (actual
> time=128691.521..128717.677 rows=1 loops=1)
>
> Output: (count(tbl_sha.et_cd))
>
> Buffers: shared hit=58948 read=2112758
>
> I/O Timings: read=357249.120
>
> -> Aggregate (cost=2728633.22..2728633.23 rows=1 width=8) (actual
> time=128691.519..128717.674 rows=1 loops=1)
>
> Output: count(tbl_sha.et_cd)
>
> Buffers: shared hit=58948 read=2112758
>
> I/O Timings: read=357249.120
>
> -> Nested Loop (cost=1000.29..2722556.76 rows=2430587 width=9)
> (actual time=2.364..128350.279 rows=2613500 loops=1)
>
> Output: tbl_sha.et_cd
>
> Buffers: shared hit=58948 read=2112758
>
> I/O Timings: read=357249.120
>
> -> Index Only Scan using tbl_inf_pkc on mtpdb.tbl_inf
> (cost=0.29..8.31 rows=1 width=9) (actual time=0.046..0.051 rows=1 loops=1)
>
> Output: tbl_inf.ms_cd, tbl_inf.ry_cd
>
> Index Cond: ((tbl_inf.ms_cd = 'MLD009'::bpchar) AND
> (tbl_inf.ry_cd = '0001'::bpchar))
>
> Heap Fetches: 1
>
> Buffers: shared hit=4
>
> -> Gather (cost=1000.00..2698242.58 rows=2430587 width=18)
> (actual time=2.315..127773.087 rows=2613500 loops=1)
>
> Output: tbl_sha.et_cd, tbl_sha.etrys
>
> Workers Planned: 2
>
> Workers Launched: 2
>
> Buffers: shared hit=58944 read=2112758
>
> I/O Timings: read=357249.120
>
> -> Parallel Seq Scan on mtpdb.tbl_sha
> (cost=0.00..2454183.88 rows=1012745 width=18) (actual
> time=952.728..127583.089 rows=871167 loops=3)
>
> Output: tbl_sha.et_cd, tbl_sha.etrys
>
> Filter: ((tbl_sha.ms_cd = 'MLD009'::bpchar) AND
> (tbl_sha.etrys = '0001'::bpchar))
>
> Rows Removed by Filter: 14062278
>
> Buffers: shared hit=58944 read=2112758
>
> I/O Timings: read=357249.120
>
> Worker 0: actual time=1432.292..127762.181
> rows=988036 loops=1
>
> Buffers: shared hit=17875 read=706862
>
> I/O Timings: read=119193.744
>
> Worker 1: actual time=1425.878..127786.777
> rows=992381 loops=1
>
> Buffers: shared hit=19813 read=706359
>
> I/O Timings: read=119386.899
>
> Planning:
>
> Buffers: shared hit=42
>
> Planning Time: 1.024 ms
>
> Execution Time: 128717.731 ms
>
>
I think the whole query can just:
select COUNT(ET_CD)
from TBL_SHA
WHERE TBL_SHA.MS_CD = 'MLD009' AND TBL_SHA.ETRYS = '0001';
> and TBL_SHA.ETRYS in
>(select TBL_INF.RY_CD
> from TBL_INF
> WHERE TBL_INF.MS_CD = 'MLD009'
>AND TBL_INF.RY_CD = '0001'
>)
if subquery after IN clause part, no rows returned then the whole
query would return zero row.
if many duplicates rows returned, then there is no point of evaluate
something like {1 in (1,1,1,1,1,1,)}
Re: Grant all privileges to user on a database
On 7/24/23 09:09, Tom Lane wrote: "David G. Johnston" writes: The error message is misleading, you can’t directly create tables in a database, you must create them in a schema and the permission to do so is granted to the role on the schema, not the database. The actual server message is going to be just ERROR: permission denied for schema public I'm not sure where the "Are you sure ..." bit came from, but it wasn't Postgres. Anyway, backing up two steps, it looks like drupal hasn't yet adapted to the v15 change that removed default create privileges on the public schema (for security reasons). You might see if a newer drupal release is available. If not, the best bet would likely be GRANT ALL ON SCHEMA public TO public; I'd have naively expected "GRANT ALL ON SCHEMA public TO public; " to be taken care of by "GRANT ALL PRIVILEGES ON DATABASE cbdevdb TO cbdevdbadmin;". -- Born in Arizona, moved to Babylonia.
Re: [Beginner Question]Is there way to test the postgres's kernel function?
On Mon, Jul 24, 2023 at 2:43 AM Wen Yi wrote: > Hi community, > I am learning the kernel of the postgres, and I want to test the > make_relative_path function to know what it's operational principle.(I can > read it's code, and I want to actually run it) > But I really don't know how to do it, wrtite a extension? > > Can someone give me some advice? > > A formal extension seems like overkill. PostgreSQL provides an ability to write customer user-space functions in C. You ought to be able to leverage that for this specific task. https://www.postgresql.org/docs/current/xfunc-c.html David J.
Re: Grant all privileges to user on a database
On Mon, Jul 24, 2023 at 7:52 AM Ron wrote: > On 7/24/23 09:09, Tom Lane wrote: > > > GRANT ALL ON SCHEMA public TO public; > > I'd have naively expected "GRANT ALL ON SCHEMA public TO public; " to be > taken care of by "GRANT ALL PRIVILEGES ON DATABASE cbdevdb TO > cbdevdbadmin;". > > I'm quite happy that such a command doesn't go ahead and grant read, write, and execute privileges on every table, function, and view in the database. The thing that does what you describe is called SUPERUSER. David J.
Re: How to improve the performance of my SQL query？
On Mon, 2023-07-24 at 17:54 +0800, gzh wrote: > EXPLAIN (ANALYZE ON, VERBOSE ON, COSTS ON, BUFFERS ON) I asked for EXPLAIN (ANALYZE, BUFFERS, SETTINGS) ... Or do you have a version that is too old for SETTINGS? One other idea: check if the index is INVALID (this will be visible if you run "\d tablenane" in "psql"). Invalid indexes won't be used. Yours, Laurenz Albe
Re: Duplicate Primary keys in postgresql tables
Hi Ken and Adrian, Thanks for the useful suggestions! We've investigated a bit more on the collations and we're almost certain it is the RC: For Debian 11.11: user=# select 'a' > 'A'; ?column? -- f (1 row) user=# select 'a' < 'A'; ?column? -- t (1 row) For Alpine 11.19: user=# select 'a' > 'A'; ?column? -- t (1 row) user=# select 'a' < 'A'; ?column? -- f (1 row) We'll fix the image issue and re-index the affected tables. Regards, De On Sat, Jul 22, 2023 at 11:02 AM Adrian Klaver wrote: > On 7/22/23 10:11, De Lan wrote: > > Hi pgsql community, > > > > Recently we found in a postgresql 11.19.0 alpine table there are two > > rows with duplicate primary keys. > > > > > > > *Our questions:* > > > > Any ideas on what might cause this behavior other than the collation? if > > it is a well-known issue in the pgsql community or it really is the > > coalition that's the root cause, do we have mitigation for this kind of > > issue from happening in future? > > Don't blindly update containers, test first. > > > > > > > > > De, > > > > Thanks! > > > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Setting Auto Commit off in C API
Hello Is there a way to turn off auto commit using the C API? Thanks
Re: How to improve the performance of my SQL query？
>I asked for EXPLAIN (ANALYZE, BUFFERS, SETTINGS) ... >Or do you have a version that is too old for SETTINGS? Sorry. Please refer to the following execution plan. EXPLAIN (ANALYZE, BUFFERS, SETTINGS) select COUNT(ET_CD) from TBL_SHA WHERE TBL_SHA.MS_CD = 'MLD009' and TBL_SHA.ETRYS in (select TBL_INF.RY_CD from TBL_INF WHERE TBL_INF.MS_CD = 'MLD009' AND TBL_INF.RY_CD = '0001' ) - Execution Plan - Buffers: shared hit=13 read=2171693 I/O Timings: read=365863.877 -> Aggregate (cost=2728633.22..2728633.23 rows=1 width=8) (actual time=131595.624..131599.529 rows=1 loops=1) Buffers: shared hit=13 read=2171693 I/O Timings: read=365863.877 -> Nested Loop (cost=1000.29..2722556.76 rows=2430587 width=9) (actual time=2.341..131256.445 rows=2513500 loops=1) Buffers: shared hit=13 read=2171693 I/O Timings: read=365863.877 -> Index Only Scan using tbl_inf_pkc on tbl_inf (cost=0.29..8.31 rows=1 width=9) (actual time=1.471..1.476 rows=1 loops=1) Index Cond: ((ms_cd = 'MLD009'::bpchar) AND (ry_cd = '0001'::bpchar)) Heap Fetches: 1 Buffers: shared hit=2 read=2 I/O Timings: read=1.412 -> Gather (cost=1000.00..2698242.58 rows=2430587 width=18) (actual time=0.866..130696.440 rows=2513500 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=11 read=2171691 I/O Timings: read=365862.464 -> Parallel Seq Scan on tbl_sha (cost=0.00..2454183.88 rows=1012745 width=18) (actual time=0.215..130476.981 rows=837833 loops=3) Filter: ((ms_cd = 'MLD009'::bpchar) AND (etrys = '0001'::bpchar)) Rows Removed by Filter: 13728945 Buffers: shared hit=11 read=2171691 I/O Timings: read=365862.464 Settings: effective_cache_size = '1886088kB', jit = 'off', search_path = '"$user", mdb' Planning: Buffers: shared hit=167 read=7 I/O Timings: read=2.735 Planning Time: 3.733 ms Execution Time: 131599.594 ms At 2023-07-24 23:58:50, "Laurenz Albe" wrote: >On Mon, 2023-07-24 at 17:54 +0800, gzh wrote: >> EXPLAIN (ANALYZE ON, VERBOSE ON, COSTS ON, BUFFERS ON) > >I asked for EXPLAIN (ANALYZE, BUFFERS, SETTINGS) ... >Or do you have a version that is too old for SETTINGS? > >One other idea: check if the index is INVALID (this will >be visible if you run "\d tablenane" in "psql"). >Invalid indexes won't be used. > >Yours, >Laurenz Albe
Re: How to improve the performance of my SQL query？
Thank you for your reply.
>I think the whole query can just:
>select COUNT(ET_CD)
>from TBL_SHA
>WHERE TBL_SHA.MS_CD = 'MLD009' AND TBL_SHA.ETRYS = '0001';
This is related to the business logic.
>if many duplicates rows returned, then there is no point of evaluate
>something like {1 in (1,1,1,1,1,1,)}
Because the primary key of the 'tbl_inf' table only consists of 'ms_cd' and
'ry_cd' columns, the subquery will not return duplicate rows.
At 2023-07-24 22:42:01, "jian he" wrote:
>On Mon, Jul 24, 2023 at 5:54 PM gzh wrote:
>>
>> >Did you change any parameters that have an impact on query planning?
>>
>> >You can see that in the output of EXPLAIN (ANALYZE, BUFFERS, SETTINGS).
>>
>> I added some parameters and re-executed the Execution Plan.
>>
>> Except for the index not taking effect, I still don't know the reason why
>> the index is not working.
>>
>> Is it because there is too much data that meets the conditions?
>>
>>
>> EXPLAIN (ANALYZE ON, VERBOSE ON, COSTS ON, BUFFERS ON)
>>
>> select COUNT(ET_CD)
>>
>> from TBL_SHA
>>
>> WHERE TBL_SHA.MS_CD = 'MLD009'
>>
>> and TBL_SHA.ETRYS in
>>
>>(select TBL_INF.RY_CD
>>
>> from TBL_INF
>>
>> WHERE TBL_INF.MS_CD = 'MLD009'
>>
>>AND TBL_INF.RY_CD = '0001'
>>
>>)
>>
>>
>> - Execution Plan -
>>
>> Limit (cost=2728633.22..2728633.23 rows=1 width=8) (actual
>> time=128691.521..128717.677 rows=1 loops=1)
>>
>> Output: (count(tbl_sha.et_cd))
>>
>> Buffers: shared hit=58948 read=2112758
>>
>> I/O Timings: read=357249.120
>>
>> -> Aggregate (cost=2728633.22..2728633.23 rows=1 width=8) (actual
>> time=128691.519..128717.674 rows=1 loops=1)
>>
>> Output: count(tbl_sha.et_cd)
>>
>> Buffers: shared hit=58948 read=2112758
>>
>> I/O Timings: read=357249.120
>>
>> -> Nested Loop (cost=1000.29..2722556.76 rows=2430587 width=9)
>> (actual time=2.364..128350.279 rows=2613500 loops=1)
>>
>> Output: tbl_sha.et_cd
>>
>> Buffers: shared hit=58948 read=2112758
>>
>> I/O Timings: read=357249.120
>>
>> -> Index Only Scan using tbl_inf_pkc on mtpdb.tbl_inf
>> (cost=0.29..8.31 rows=1 width=9) (actual time=0.046..0.051 rows=1 loops=1)
>>
>> Output: tbl_inf.ms_cd, tbl_inf.ry_cd
>>
>> Index Cond: ((tbl_inf.ms_cd = 'MLD009'::bpchar) AND
>> (tbl_inf.ry_cd = '0001'::bpchar))
>>
>> Heap Fetches: 1
>>
>> Buffers: shared hit=4
>>
>> -> Gather (cost=1000.00..2698242.58 rows=2430587 width=18)
>> (actual time=2.315..127773.087 rows=2613500 loops=1)
>>
>> Output: tbl_sha.et_cd, tbl_sha.etrys
>>
>> Workers Planned: 2
>>
>> Workers Launched: 2
>>
>> Buffers: shared hit=58944 read=2112758
>>
>> I/O Timings: read=357249.120
>>
>> -> Parallel Seq Scan on mtpdb.tbl_sha
>> (cost=0.00..2454183.88 rows=1012745 width=18) (actual
>> time=952.728..127583.089 rows=871167 loops=3)
>>
>> Output: tbl_sha.et_cd, tbl_sha.etrys
>>
>> Filter: ((tbl_sha.ms_cd = 'MLD009'::bpchar) AND
>> (tbl_sha.etrys = '0001'::bpchar))
>>
>> Rows Removed by Filter: 14062278
>>
>> Buffers: shared hit=58944 read=2112758
>>
>> I/O Timings: read=357249.120
>>
>> Worker 0: actual time=1432.292..127762.181
>> rows=988036 loops=1
>>
>> Buffers: shared hit=17875 read=706862
>>
>> I/O Timings: read=119193.744
>>
>> Worker 1: actual time=1425.878..127786.777
>> rows=992381 loops=1
>>
>> Buffers: shared hit=19813 read=706359
>>
>> I/O Timings: read=119386.899
>>
>> Planning:
>>
>> Buffers: shared hit=42
>>
>> Planning Time: 1.024 ms
>>
>> Execution Time: 128717.731 ms
>>
>>
>
>I think the whole query can just:
>select COUNT(ET_CD)
>from TBL_SHA
>WHERE TBL_SHA.MS_CD = 'MLD009' AND TBL_SHA.ETRYS = '0001';
>
>> and TBL_SHA.ETRYS in
>>(select TBL_INF.RY_CD
>> from TBL_INF
>> WHERE TBL_INF.MS_CD = 'MLD009'
>>AND TBL_INF.RY_CD = '0001'
>>)
>
>if subquery after IN clause part, no rows returned then the whole
>query would return zero row.
>if many duplicates rows returned, then there is no point of evaluate
>something like {1 in (1,1,1,1,1,1,)}
Re: How to improve the performance of my SQL query？
On Tue, 2023-07-25 at 11:11 +0800, gzh wrote: > > I asked for EXPLAIN (ANALYZE, BUFFERS, SETTINGS) ... > > Or do you have a version that is too old for SETTINGS? > Sorry. Please refer to the following execution plan. > > [...] > Settings: effective_cache_size = '1886088kB', jit = 'off', search_path = > '"$user", mdb' > [...] No unusual settings. So that theory is dead. > > One other idea: check if the index is INVALID (this will > > be visible if you run "\d tablenane" in "psql"). > > Invalid indexes won't be used. Did you check that? After that, I am out of ideas. Yours, Laurenz Albe
Re: Setting Auto Commit off in C API
On Mon, 2023-07-24 at 18:54 -0700, Badri Subramaniam wrote: > Is there a way to turn off auto commit using the C API? No. Yours, Laurenz Albe
