Re: How to improve the performance of my SQL query？
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)) > Rows Removed by Filter: 32000325 > Planning Time: 0.162 ms > Execution Time: 124168.838 ms Thanks. That should definitely use a b-tree index defined on (ms_cd, etrsys). Did you change any parameters that have an impact on query planning? You can see that in the output of EXPLAIN (ANALYZE, BUFFERS, SETTINGS). Yours, Laurenz Albe
Re: pageinspect bt_page_items doc
Hi, On Mon, Jul 24, 2023 at 08:57:05AM +0800, jian he wrote: > hi. > > https://www.postgresql.org/docs/current/pageinspect.html#id-1.11.7.34.6 > > > This is a B-tree leaf page. All tuples that point to the table happen to be > > posting list tuples (all of which store a total of 100 6 byte TIDs). > > There is also a “high key” tuple at itemoffset number 1. ctid is used to > > store encoded information about each tuple in this example, though > > leaf page tuples often store a heap TID directly in the ctid field instead. > > tids is the list of TIDs stored as a posting list. > > (all of which store a total of 100 6 byte TIDs) > I think the meaning is something like: > (all of which store a total of 100 TIDs, each TID is 6 byte long.) Yes that's what it means. > What's the meaning of (16, 8292)? After looking around. I'm still confused. > Would it be better to add an explanation about (16, 8292) to the docs? I'm not sure how easy it would be to document it. If you want more detail about "encoded information about each tuple" you need to look at the btree implementation, in src/include/access/nbtree.h. For instance, to get the number of items in a posting list, you see the BTreeTupleGetNPosting() function, which basically returns ctid & 0X0FFF. And 8292 & 0X0FFF is 100, which is the mentioned number of tids. If you look around line 462 of the same file (near the BT_OFFSET_MASK) you will see all the other usage for the ctid in case of a posting list, like knowing whether the IndexTuple is a posting list of not.
pageinspect bt_page_items doc
hi. https://www.postgresql.org/docs/current/pageinspect.html#id-1.11.7.34.6 > This is a B-tree leaf page. All tuples that point to the table happen to be > posting list tuples (all of which store a total of 100 6 byte TIDs). > There is also a “high key” tuple at itemoffset number 1. ctid is used to > store encoded information about each tuple in this example, though > leaf page tuples often store a heap TID directly in the ctid field instead. > tids is the list of TIDs stored as a posting list. (all of which store a total of 100 6 byte TIDs) I think the meaning is something like: (all of which store a total of 100 TIDs, each TID is 6 byte long.) What's the meaning of (16, 8292)? After looking around. I'm still confused. Would it be better to add an explanation about (16, 8292) to the docs?
Re: Effects of dropping a large table
On 7/23/23 05:27, Peter J. Holzer wrote: On 2023-07-23 06:09:03 -0400, Gus Spier wrote: Ah! Truncating a table does not entail all of WAL processes. From the documentation, "TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables." https://www.postgresql.org/docs/14/sql-truncate.html I assumed that by "deleting the now empty table" you meant DROPing it. (Performing a «DELETE FROM t» just after a «TRUNCATE t» would obviously be pointless). So let me rephrase the question: What's the advantage of TRUNCATE t DROP t over just DROP t Catalog or serialization locking? (I don't know; just asking.) -- Born in Arizona, moved to Babylonia.
Re: Effects of dropping a large table
On 2023-07-23 06:09:03 -0400, Gus Spier wrote: > Ah! Truncating a table does not entail all of WAL processes. From the > documentation, "TRUNCATE quickly removes all rows from a set of tables. It has > the same effect as an unqualified DELETE on each table, but since it does not > actually scan the tables it is faster. Furthermore, it reclaims disk space > immediately, rather than requiring a subsequent VACUUM operation. This is most > useful on large tables." https://www.postgresql.org/docs/14/sql-truncate.html I assumed that by "deleting the now empty table" you meant DROPing it. (Performing a «DELETE FROM t» just after a «TRUNCATE t» would obviously be pointless). So let me rephrase the question: What's the advantage of TRUNCATE t DROP t over just DROP t hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Effects of dropping a large table
Ah! Truncating a table does not entail all of WAL processes. From the documentation, "TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables." https://www.postgresql.org/docs/14/sql-truncate.html Regards, Gus On Sun, Jul 23, 2023 at 5:51 AM Peter J. Holzer wrote: > On 2023-07-22 16:37:39 -0400, Gus Spier wrote: > > Isn’t this a perfect opportunity to use the TRUNCATE command to > > quickly remove the data? And follow up by deleting the now empty > > tables? > > What's the advantage of first truncating and then deleting a table over > just deleting it? > > hp > > -- >_ | Peter J. Holzer| Story must make more sense than reality. > |_|_) || > | | | h...@hjp.at |-- Charles Stross, "Creative writing > __/ | http://www.hjp.at/ | challenge!" >
Re: Effects of dropping a large table
On 2023-07-22 16:37:39 -0400, Gus Spier wrote: > Isn’t this a perfect opportunity to use the TRUNCATE command to > quickly remove the data? And follow up by deleting the now empty > tables? What's the advantage of first truncating and then deleting a table over just deleting it? hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
