[PATCHES] pgstattuple extension for indexes
This is an extension of pgstattuple to query information from indexes. It supports btree, hash and gist. Gin is not supported. It scans only index pages and does not read corresponding heap tuples. Therefore, 'dead_tuple' means the number of tuples with LP_DELETE flag. Also, I added an experimental feature for btree indexes. It checks fragmentation factor of indexes. If an leaf has the right link on the next adjacent page in the file, it is assumed to be continuous (not fragmented). It will help us to decide when to REINDEX. Suggestions welcome. $ pgbench -i $ pgbench -n -t 100 -c 10 # select * from pgstattuple('accounts_pkey'); NOTICE: 0.36% fragmented HINT: continuous=273, forward=1, backward=0 -[ RECORD 1 ]--+ table_len | 2260992 tuple_count| 100996 -- 996 tuples are dead practically, tuple_len | 1615936 but no LP_DELETE yet. tuple_percent | 71.47 dead_tuple_count | 4 dead_tuple_len | 64 -- 64 tuples are marked as LP_DELETE. dead_tuple_percent | 0 free_space | 208188 free_percent | 9.21 Regards, --- ITAGAKI Takahiro NTT Open Source Software Center pgstattuple.patch Description: Binary data ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] pgstattuple extension for indexes
Patch applied. Thanks. --- ITAGAKI Takahiro wrote: > This is an extension of pgstattuple to query information from indexes. > It supports btree, hash and gist. Gin is not supported. > It scans only index pages and does not read corresponding heap tuples. > Therefore, 'dead_tuple' means the number of tuples with LP_DELETE flag. > > Also, I added an experimental feature for btree indexes. It checks > fragmentation factor of indexes. If an leaf has the right link on the next > adjacent page in the file, it is assumed to be continuous (not fragmented). > It will help us to decide when to REINDEX. > > Suggestions welcome. > > > $ pgbench -i > $ pgbench -n -t 100 -c 10 > # select * from pgstattuple('accounts_pkey'); > NOTICE: 0.36% fragmented > HINT: continuous=273, forward=1, backward=0 > -[ RECORD 1 ]--+ > table_len | 2260992 > tuple_count| 100996 -- 996 tuples are dead practically, > tuple_len | 1615936 but no LP_DELETE yet. > tuple_percent | 71.47 > dead_tuple_count | 4 > dead_tuple_len | 64 -- 64 tuples are marked as LP_DELETE. > dead_tuple_percent | 0 > free_space | 208188 > free_percent | 9.21 > > > > Regards, > --- > ITAGAKI Takahiro > NTT Open Source Software Center [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] pgstattuple extension for indexes
Bruce Momjian <[EMAIL PROTECTED]> writes: > Patch applied. Thanks. >> Also, I added an experimental feature for btree indexes. It checks >> fragmentation factor of indexes. If an leaf has the right link on the next >> adjacent page in the file, it is assumed to be continuous (not fragmented). >> It will help us to decide when to REINDEX. This was done in an entirely unacceptable way, to wit ereport(NOTICE, (errmsg("%.2f%% fragmented", 100.0 * (stat.forward + stat.backward) / (stat.continuous + stat.forward + stat.backward)), errhint("continuous=%llu, forward=%llu, backward=%llu", stat.continuous, stat.forward, stat.backward))); The really serious problem with reporting this info via NOTICE is that there's no way for a program to get its hands on the info. The output tuple format needs to be extended instead. The lesser problem that drew my attention is that %llu is unportable (and in fact draws gcc warnings for me; did you ignore that?). But using UINT64_FORMAT instead would create a headache for translation because the string would vary across platforms. I won't bother correcting the violations of message style guidelines, because this code simply has to go away. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] pgstattuple extension for indexes
Tom Lane <[EMAIL PROTECTED]> wrote: > >> Also, I added an experimental feature for btree indexes. It checks > >> fragmentation factor of indexes. > The really serious problem with reporting this info via NOTICE is that > there's no way for a program to get its hands on the info. The output > tuple format needs to be extended instead. Ok, I added 'fragmented_percent' field to the output tuple. This information will help us to decide when to do REINDEX. However, it is only avaliable for btree index presently. Other indexes should have equivalent information, but I don't know how to determine it. BTW, should we change VACUUM VERBOSE in the same way? If we do so, autovacuum can handle the reports of VACUUM VERBOSE and plan when to do VACUUM FULL, REINDEX and/or CLUSTER using the information. Is this worth doing? Regards, --- ITAGAKI Takahiro NTT Open Source Software Center pgstattuple-0724.patch Description: Binary data ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] pgstattuple extension for indexes
ITAGAKI Takahiro wrote: > Tom Lane <[EMAIL PROTECTED]> wrote: > > > >> Also, I added an experimental feature for btree indexes. It checks > > >> fragmentation factor of indexes. > > > The really serious problem with reporting this info via NOTICE is that > > there's no way for a program to get its hands on the info. The output > > tuple format needs to be extended instead. > > Ok, I added 'fragmented_percent' field to the output tuple. This > information will help us to decide when to do REINDEX. > However, it is only avaliable for btree index presently. Other indexes > should have equivalent information, but I don't know how to determine it. BTW while you're handling this, why not change the function to use OUT parameters instead of having to CREATE TYPE to handle the return type? I think it is easier to handle ... One question I had was: in the percentage of fragmentation, is higher better or lower better? (I'm guessing lower is better, but this should be mentioned in the docs) > BTW, should we change VACUUM VERBOSE in the same way? If we do so, > autovacuum can handle the reports of VACUUM VERBOSE and plan when to > do VACUUM FULL, REINDEX and/or CLUSTER using the information. > Is this worth doing? You mean having VACUUM VERBOSE return a result set? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] pgstattuple extension for indexes
Alvaro Herrera <[EMAIL PROTECTED]> writes: > ITAGAKI Takahiro wrote: >> BTW, should we change VACUUM VERBOSE in the same way? If we do so, >> autovacuum can handle the reports of VACUUM VERBOSE and plan when to >> do VACUUM FULL, REINDEX and/or CLUSTER using the information. >> Is this worth doing? > You mean having VACUUM VERBOSE return a result set? To me, the point of VACUUM VERBOSE is mostly to give you some reassurance that it's making progress. If it were returning rows instead of notice messages, you'd lose that functionality (at least in libpq-based clients). In any case, autovacuum has other ways of getting the information without needing a change in user-visible behavior. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] pgstattuple extension for indexes
Hi, I'm working on an utility for b-tree index, called `pgstatindex`. It reports b-tree index statistics like a pgstattuple as below. pgbench=# \x Expanded display is on. pgbench=# SELECT * FROM pgstatindex('accounts_pkey1'); -[ RECORD 1 ]--+- version| 2 tree_level | 2 index_size | 17956864 root_block_no | 361 internal_pages | 8 leaf_pages | 2184 empty_pages| 0 deleted_pages | 0 avg_leaf_density | 90.07 leaf_fragmentation | 0 pgbench=# I want to make this to contrib module like a pgstattuple, and to make this open to public in a few days. Do you think this is useful? 2006/7/24, ITAGAKI Takahiro <[EMAIL PROTECTED]>: Tom Lane <[EMAIL PROTECTED]> wrote: > >> Also, I added an experimental feature for btree indexes. It checks > >> fragmentation factor of indexes. > The really serious problem with reporting this info via NOTICE is that > there's no way for a program to get its hands on the info. The output > tuple format needs to be extended instead. Ok, I added 'fragmented_percent' field to the output tuple. This information will help us to decide when to do REINDEX. However, it is only avaliable for btree index presently. Other indexes should have equivalent information, but I don't know how to determine it. BTW, should we change VACUUM VERBOSE in the same way? If we do so, autovacuum can handle the reports of VACUUM VERBOSE and plan when to do VACUUM FULL, REINDEX and/or CLUSTER using the information. Is this worth doing? Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] pgstattuple extension for indexes
Satoshi Nagayasu wrote: > Hi, > > I'm working on an utility for b-tree index, called `pgstatindex`. > > It reports b-tree index statistics like a pgstattuple as below. > > pgbench=# \x > Expanded display is on. > pgbench=# SELECT * FROM pgstatindex('accounts_pkey1'); > -[ RECORD 1 ]--+- > version| 2 > tree_level | 2 > index_size | 17956864 > root_block_no | 361 > internal_pages | 8 > leaf_pages | 2184 > empty_pages| 0 > deleted_pages | 0 > avg_leaf_density | 90.07 > leaf_fragmentation | 0 > > pgbench=# > > > I want to make this to contrib module like a pgstattuple, > and to make this open to public in a few days. > > Do you think this is useful? Yes, for performance debugging, I think. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] pgstattuple extension for indexes
Satoshi Nagayasu wrote: > Hi, > > I'm working on an utility for b-tree index, called `pgstatindex`. Does it make sense to merge the pgstatindex stuff with pgstattuple, and have the fragmentation report into pgstatindex instead of pgstattuple itself? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] pgstattuple extension for indexes
Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Satoshi Nagayasu wrote: > > I'm working on an utility for b-tree index, called `pgstatindex`. > > Does it make sense to merge the pgstatindex stuff with pgstattuple, and > have the fragmentation report into pgstatindex instead of pgstattuple > itself? It sounds good. We will have two separate commands: - pgstattuple: returns tuple-level information - pgstatindex: returns page-level information We can use tuple-level info to check LP_DELETE flags on index tuples, and use page-level info to check needs for REINDEX. Do we add pgstatindex as a new contrib module, or merge it into contrib/pgstattuple? Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] pgstattuple extension for indexes
ITAGAKI Takahiro <[EMAIL PROTECTED]> writes: > Do we add pgstatindex as a new contrib module, > or merge it into contrib/pgstattuple? I believe Alvaro was suggesting that you should add it as an additional SQL function within contrib/pgstattuple. That'd be my advice too --- I don't see a reason to break this out as a separate contrib module. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] pgstattuple extension for indexes
Hi folks, As I said on -PATCHES, I've been working on an utility to get a b-tree index information. I'm happy to introduce my new functions to you. pgstattuple module provides a `pgstatindex()`, and other small functions, which allow you to get b-tree internal information. I believe this module will be helpful to know b-tree index deeply. So please try it, send comment to me, and have fun. Thanks, -- NAGAYASU Satoshi <[EMAIL PROTECTED]> - pgbench=# \x Expanded display is on. pgbench=# SELECT * FROM pgstatindex('accounts_pkey'); -[ RECORD 1 ]--+ version| 2 tree_level | 1 index_size | 3588096 root_block_no | 3 internal_pages | 0 leaf_pages | 437 empty_pages| 0 deleted_pages | 0 avg_leaf_density | 59.5 leaf_fragmentation | 49.89 - pgstatindex.tar.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] pgstattuple extension for indexes
I thought these new functions were going to be merged into /contrib/pgstattuple. --- satoshi nagayasu wrote: > Hi folks, > > As I said on -PATCHES, I've been working on an utility to get > a b-tree index information. I'm happy to introduce > my new functions to you. > > pgstattuple module provides a `pgstatindex()`, and other small > functions, which allow you to get b-tree internal information. > I believe this module will be helpful to know b-tree index deeply. > > So please try it, send comment to me, and have fun. > > Thanks, > -- > NAGAYASU Satoshi <[EMAIL PROTECTED]> > > - > pgbench=# \x > Expanded display is on. > pgbench=# SELECT * FROM pgstatindex('accounts_pkey'); > -[ RECORD 1 ]--+ > version| 2 > tree_level | 1 > index_size | 3588096 > root_block_no | 3 > internal_pages | 0 > leaf_pages | 437 > empty_pages| 0 > deleted_pages | 0 > avg_leaf_density | 59.5 > leaf_fragmentation | 49.89 > - > > [ application/x-gzip is not supported, skipping... ] > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] pgstattuple extension for indexes
nagayasu-san, This looks good, but we would like the code added to /contrib/pgstattuple, rather than it being its own /contrib module. Can you make that adjustment? Thanks. --- satoshi nagayasu wrote: > Hi folks, > > As I said on -PATCHES, I've been working on an utility to get > a b-tree index information. I'm happy to introduce > my new functions to you. > > pgstattuple module provides a `pgstatindex()`, and other small > functions, which allow you to get b-tree internal information. > I believe this module will be helpful to know b-tree index deeply. > > So please try it, send comment to me, and have fun. > > Thanks, > -- > NAGAYASU Satoshi <[EMAIL PROTECTED]> > > - > pgbench=# \x > Expanded display is on. > pgbench=# SELECT * FROM pgstatindex('accounts_pkey'); > -[ RECORD 1 ]--+ > version| 2 > tree_level | 1 > index_size | 3588096 > root_block_no | 3 > internal_pages | 0 > leaf_pages | 437 > empty_pages| 0 > deleted_pages | 0 > avg_leaf_density | 59.5 > leaf_fragmentation | 49.89 > - > > [ application/x-gzip is not supported, skipping... ] > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] pgstattuple extension for indexes
Bruce, I'll fix it in this week. Please wait a few days. Thanks. Bruce Momjian wrote: > nagayasu-san, > > This looks good, but we would like the code added to > /contrib/pgstattuple, rather than it being its own /contrib module. Can > you make that adjustment? Thanks. > > --- > > satoshi nagayasu wrote: >> Hi folks, >> >> As I said on -PATCHES, I've been working on an utility to get >> a b-tree index information. I'm happy to introduce >> my new functions to you. >> >> pgstattuple module provides a `pgstatindex()`, and other small >> functions, which allow you to get b-tree internal information. >> I believe this module will be helpful to know b-tree index deeply. >> >> So please try it, send comment to me, and have fun. >> >> Thanks, >> -- >> NAGAYASU Satoshi <[EMAIL PROTECTED]> >> >> - >> pgbench=# \x >> Expanded display is on. >> pgbench=# SELECT * FROM pgstatindex('accounts_pkey'); >> -[ RECORD 1 ]--+ >> version| 2 >> tree_level | 1 >> index_size | 3588096 >> root_block_no | 3 >> internal_pages | 0 >> leaf_pages | 437 >> empty_pages| 0 >> deleted_pages | 0 >> avg_leaf_density | 59.5 >> leaf_fragmentation | 49.89 >> - >> >> > > [ application/x-gzip is not supported, skipping... ] > >> ---(end of broadcast)--- >> TIP 4: Have you searched our list archives? >> >>http://archives.postgresql.org > -- NAGAYASU Satoshi <[EMAIL PROTECTED]> Phone: +81-3-3523-8122 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] pgstattuple extension for indexes
Hi all, Here is a patch to add pgstatindex functions to the pgstattuple module, which can work with 8.1.4. Please review and try it. Thanks. Satoshi Nagayasu wrote: > Bruce, > > I'll fix it in this week. Please wait a few days. > Thanks. > > Bruce Momjian wrote: >> nagayasu-san, >> >> This looks good, but we would like the code added to >> /contrib/pgstattuple, rather than it being its own /contrib module. Can >> you make that adjustment? Thanks. >> >> --- >> >> satoshi nagayasu wrote: >>> Hi folks, >>> >>> As I said on -PATCHES, I've been working on an utility to get >>> a b-tree index information. I'm happy to introduce >>> my new functions to you. >>> >>> pgstattuple module provides a `pgstatindex()`, and other small >>> functions, which allow you to get b-tree internal information. >>> I believe this module will be helpful to know b-tree index deeply. >>> >>> So please try it, send comment to me, and have fun. >>> >>> Thanks, >>> -- >>> NAGAYASU Satoshi <[EMAIL PROTECTED]> >>> >>> - >>> pgbench=# \x >>> Expanded display is on. >>> pgbench=# SELECT * FROM pgstatindex('accounts_pkey'); >>> -[ RECORD 1 ]--+ >>> version| 2 >>> tree_level | 1 >>> index_size | 3588096 >>> root_block_no | 3 >>> internal_pages | 0 >>> leaf_pages | 437 >>> empty_pages| 0 >>> deleted_pages | 0 >>> avg_leaf_density | 59.5 >>> leaf_fragmentation | 49.89 >>> - >>> >>> >> [ application/x-gzip is not supported, skipping... ] >> >>> ---(end of broadcast)--- >>> TIP 4: Have you searched our list archives? >>> >>>http://archives.postgresql.org > > -- NAGAYASU Satoshi <[EMAIL PROTECTED]> Phone: +81-3-3523-8122 diff -ruN pgstattuple.orig/Makefile pgstattuple/Makefile --- pgstattuple.orig/Makefile 2006-08-10 19:22:47.0 +0900 +++ pgstattuple/Makefile2006-08-10 19:24:05.0 +0900 @@ -6,7 +6,7 @@ # #- -SRCS = pgstattuple.c +SRCS = pgstattuple.c pgstatindex.c MODULE_big = pgstattuple OBJS = $(SRCS:.c=.o) diff -ruN pgstattuple.orig/pgstatindex.c pgstattuple/pgstatindex.c --- pgstattuple.orig/pgstatindex.c 1970-01-01 09:00:00.0 +0900 +++ pgstattuple/pgstatindex.c 2006-08-11 17:51:26.0 +0900 @@ -0,0 +1,714 @@ +/* + * pgstatindex + * + * Copyright (c) 2006 Satoshi Nagayasu <[EMAIL PROTECTED]> + * + * Permission to use, copy, modify, and distribute this software and + * its documentation for any purpose, without fee, and without a + * written agreement is hereby granted, provided that the above + * copyright notice and this paragraph and the following two + * paragraphs appear in all copies. + * + * IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT, + * INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS + * DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED + * OF THE POSSIBILITY OF SUCH DAMAGE. + * + * THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT + * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR + * A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS + * IS" BASIS, AND THE AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, + * SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. + */ + +#include "postgres.h" + +#include "fmgr.h" +#include "funcapi.h" +#include "access/heapam.h" +#include "access/itup.h" +#include "access/nbtree.h" +#include "access/transam.h" +#include "catalog/namespace.h" +#include "catalog/pg_type.h" +#include "utils/builtins.h" +#include "utils/inval.h" + +PG_FUNCTION_INFO_V1(pgstatindex); +PG_FUNCTION_INFO_V1(bt_metap); +PG_FUNCTION_INFO_V1(bt_page_items); +PG_FUNCTION_INFO_V1(bt_page_stats); +PG_FUNCTION_INFO_V1(pg_relpages); + +extern Datum pgstatindex(PG_FUNCTION_ARGS); +extern Datum bt_metap(PG_FUNCTION_ARGS); +extern Datum bt_page_items(PG_FUNCTION_ARGS); +extern Datum bt_page_stats(PG_FUNCTION_ARGS); +extern Datum pg_relpages(PG_FUNCTION_ARGS); + +#define PGSTATINDEX_TYPE "public.pgstatindex_type" +#define PGSTATINDEX_NCOLUMNS 10 + +#define BTMETAP_TYPE "public.bt_metap_type" +#define BTMETAP_NCOLUMNS 6 + +#define BTPAGEITEMS_TYPE "public.bt_page_items_type" +#define BTPAGEITEMS_NCOLUMNS 6 + +#define BTPAGESTATS_TYPE "public.bt_page_stats_type" +#define BTPAGESTATS_NCOLUMNS 12 + + +#define IS_INDEX(r) ((r)->rd_rel->relkind == 'i') +#define IS_BTREE(r) ((r)->rd_rel->relam == BTREE_AM_OID) + +#define CHECK_PAGE_OFFSET_RANGE(page, offset) { \ +if ( !(FirstOffsetNumber<=(offset) && \ +(offset)<=PageGetMaxOffsetNumber(page)) ) \ + elog
Re: [PATCHES] pgstattuple extension for indexes
I don't see any documentation, so I assume you want me to add something to README.pgstattuple. Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Satoshi Nagayasu wrote: > Hi all, > > Here is a patch to add pgstatindex functions to the pgstattuple module, > which can work with 8.1.4. Please review and try it. Thanks. > > > Satoshi Nagayasu wrote: > > Bruce, > > > > I'll fix it in this week. Please wait a few days. > > Thanks. > > > > Bruce Momjian wrote: > >> nagayasu-san, > >> > >> This looks good, but we would like the code added to > >> /contrib/pgstattuple, rather than it being its own /contrib module. Can > >> you make that adjustment? Thanks. > >> > >> --- > >> > >> satoshi nagayasu wrote: > >>> Hi folks, > >>> > >>> As I said on -PATCHES, I've been working on an utility to get > >>> a b-tree index information. I'm happy to introduce > >>> my new functions to you. > >>> > >>> pgstattuple module provides a `pgstatindex()`, and other small > >>> functions, which allow you to get b-tree internal information. > >>> I believe this module will be helpful to know b-tree index deeply. > >>> > >>> So please try it, send comment to me, and have fun. > >>> > >>> Thanks, > >>> -- > >>> NAGAYASU Satoshi <[EMAIL PROTECTED]> > >>> > >>> - > >>> pgbench=# \x > >>> Expanded display is on. > >>> pgbench=# SELECT * FROM pgstatindex('accounts_pkey'); > >>> -[ RECORD 1 ]--+ > >>> version| 2 > >>> tree_level | 1 > >>> index_size | 3588096 > >>> root_block_no | 3 > >>> internal_pages | 0 > >>> leaf_pages | 437 > >>> empty_pages| 0 > >>> deleted_pages | 0 > >>> avg_leaf_density | 59.5 > >>> leaf_fragmentation | 49.89 > >>> - > >>> > >>> > >> [ application/x-gzip is not supported, skipping... ] > >> > >>> ---(end of broadcast)--- > >>> TIP 4: Have you searched our list archives? > >>> > >>>http://archives.postgresql.org > > > > > > > -- > NAGAYASU Satoshi <[EMAIL PROTECTED]> > Phone: +81-3-3523-8122 > diff -ruN pgstattuple.orig/Makefile pgstattuple/Makefile > --- pgstattuple.orig/Makefile 2006-08-10 19:22:47.0 +0900 > +++ pgstattuple/Makefile 2006-08-10 19:24:05.0 +0900 > @@ -6,7 +6,7 @@ > # > #- > > -SRCS = pgstattuple.c > +SRCS = pgstattuple.c pgstatindex.c > > MODULE_big = pgstattuple > OBJS = $(SRCS:.c=.o) > diff -ruN pgstattuple.orig/pgstatindex.c pgstattuple/pgstatindex.c > --- pgstattuple.orig/pgstatindex.c1970-01-01 09:00:00.0 +0900 > +++ pgstattuple/pgstatindex.c 2006-08-11 17:51:26.0 +0900 > @@ -0,0 +1,714 @@ > +/* > + * pgstatindex > + * > + * Copyright (c) 2006 Satoshi Nagayasu <[EMAIL PROTECTED]> > + * > + * Permission to use, copy, modify, and distribute this software and > + * its documentation for any purpose, without fee, and without a > + * written agreement is hereby granted, provided that the above > + * copyright notice and this paragraph and the following two > + * paragraphs appear in all copies. > + * > + * IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT, > + * INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING > + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS > + * DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED > + * OF THE POSSIBILITY OF SUCH DAMAGE. > + * > + * THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT > + * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR > + * A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS > + * IS" BASIS, AND THE AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, > + * SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. > + */ > + > +#include "postgres.h" > + > +#include "fmgr.h" > +#include "funcapi.h" > +#include "access/heapam.h" > +#include "access/itup.h" > +#include "access/nbtree.h" > +#include "access/transam.h" > +#include "catalog/namespace.h" > +#include "catalog/pg_type.h" > +#include "utils/builtins.h" > +#include "utils/inval.h" > + > +PG_FUNCTION_INFO_V1(pgstatindex); > +PG_FUNCTION_INFO_V1(bt_metap); > +PG_FUNCTION_INFO_V1(bt_page_items); > +PG_FUNCTION_INFO_V1(bt_page_stats); > +PG_FUNCTION_INFO_V1(pg_relpages); > + > +extern Datum pgstatindex(PG_FUNCTION_ARGS); > +extern Datum bt_metap(PG_FUNCTION_ARGS); > +extern Datum bt_page_items(PG_FUNCTION_ARGS); > +extern Datum bt_page_stats(PG_FUNCTION_ARGS); > +extern Datum pg_re
Re: [PATCHES] pgstattuple extension for indexes
Satoshi Nagayasu wrote: > Hi all, > > Here is a patch to add pgstatindex functions to the pgstattuple module, > which can work with 8.1.4. Please review and try it. Thanks. Huh, I bet it works with 8.1.4, but it doesn't work on CVS HEAD: /pgsql/source/00orig/contrib/pgstattuple/pgstatindex.c: In function 'GetBTPageStatistics': /pgsql/source/00orig/contrib/pgstattuple/pgstatindex.c:182: error: 'BTItem' undeclared (first use in this function) While you're at it, please consider removing C++ style comments and unused code. Formatting is way off as well, but I guess that is easily fixed with pgindent. Regarding the pg_relpages function, why do you think it's necessary? (It returns the true number of blocks of a given relation). It may belong into core given a reasonable use case, but otherwise it doesn't seem to belong into pgstatindex (or pgstattuple for that matter). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] pgstattuple extension for indexes
Alvaro, Alvaro Herrera wrote: > Huh, I bet it works with 8.1.4, but it doesn't work on CVS HEAD: > > /pgsql/source/00orig/contrib/pgstattuple/pgstatindex.c: In function > 'GetBTPageStatistics': > /pgsql/source/00orig/contrib/pgstattuple/pgstatindex.c:182: error: 'BTItem' > undeclared (first use in this function) > > > While you're at it, please consider removing C++ style comments and > unused code. > > Formatting is way off as well, but I guess that is easily fixed with > pgindent. Thanks for comments. I'm going to fix my patch from now. > Regarding the pg_relpages function, why do you think it's necessary? > (It returns the true number of blocks of a given relation). It may > belong into core given a reasonable use case, but otherwise it doesn't > seem to belong into pgstatindex (or pgstattuple for that matter). I wanted to sample some pages from the table/index, and get their statistics to know table/index conditions. I know pgstattuple() reports table statistics, however, pgstattuple() generates heavy CPU and I/O load. When we need to sample some pages from table/index, we need to know true number of blocks. I have another function, called pgstatpage(), to get information inside a single block/page statistics of the table. pg_relpages() will be used with this. Sorry for not mentioned in previous post about pgstatpage(), but I've remembered about it just now. Many memories in my brain have already `paged-out` (too busy in last few months), and some of them got `out-of-memory`. :^) Thanks. -- NAGAYASU Satoshi <[EMAIL PROTECTED]> Phone: +81-3-3523-8122 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] pgstattuple extension for indexes
Bruce, Attached patch has been cleaned up, and modified to be able to work with CVS HEAD. Thanks. Satoshi Nagayasu wrote: > Alvaro, > > Alvaro Herrera wrote: >> Huh, I bet it works with 8.1.4, but it doesn't work on CVS HEAD: >> >> /pgsql/source/00orig/contrib/pgstattuple/pgstatindex.c: In function >> 'GetBTPageStatistics': >> /pgsql/source/00orig/contrib/pgstattuple/pgstatindex.c:182: error: 'BTItem' >> undeclared (first use in this function) >> >> >> While you're at it, please consider removing C++ style comments and >> unused code. >> >> Formatting is way off as well, but I guess that is easily fixed with >> pgindent. > > Thanks for comments. I'm going to fix my patch from now. > >> Regarding the pg_relpages function, why do you think it's necessary? >> (It returns the true number of blocks of a given relation). It may >> belong into core given a reasonable use case, but otherwise it doesn't >> seem to belong into pgstatindex (or pgstattuple for that matter). > > I wanted to sample some pages from the table/index, and get their statistics > to know table/index conditions. I know pgstattuple() reports table > statistics, however, pgstattuple() generates heavy CPU and I/O load. > > When we need to sample some pages from table/index, we need to know > true number of blocks. > > I have another function, called pgstatpage(), to get information inside > a single block/page statistics of the table. pg_relpages() will be used > with this. > > Sorry for not mentioned in previous post about pgstatpage(), > but I've remembered about it just now. > > Many memories in my brain have already `paged-out` (too busy in last few > months), > and some of them got `out-of-memory`. :^) > > Thanks. -- NAGAYASU Satoshi <[EMAIL PROTECTED]> Phone: +81-3-3523-8122 diff -ruN pgstattuple.orig/Makefile pgstattuple/Makefile --- pgstattuple.orig/Makefile 2006-02-27 21:54:40.0 +0900 +++ pgstattuple/Makefile2006-08-14 09:28:58.0 +0900 @@ -6,7 +6,7 @@ # #- -SRCS = pgstattuple.c +SRCS = pgstattuple.c pgstatindex.c MODULE_big = pgstattuple OBJS = $(SRCS:.c=.o) diff -ruN pgstattuple.orig/pgstatindex.c pgstattuple/pgstatindex.c --- pgstattuple.orig/pgstatindex.c 1970-01-01 09:00:00.0 +0900 +++ pgstattuple/pgstatindex.c 2006-08-14 11:24:23.0 +0900 @@ -0,0 +1,706 @@ +/* + * pgstatindex + * + * Copyright (c) 2006 Satoshi Nagayasu <[EMAIL PROTECTED]> + * + * Permission to use, copy, modify, and distribute this software and + * its documentation for any purpose, without fee, and without a + * written agreement is hereby granted, provided that the above + * copyright notice and this paragraph and the following two + * paragraphs appear in all copies. + * + * IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT, + * INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS + * DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED + * OF THE POSSIBILITY OF SUCH DAMAGE. + * + * THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT + * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR + * A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS + * IS" BASIS, AND THE AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, + * SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. + */ + +#include "postgres.h" + +#include "fmgr.h" +#include "funcapi.h" +#include "access/heapam.h" +#include "access/itup.h" +#include "access/nbtree.h" +#include "access/transam.h" +#include "catalog/namespace.h" +#include "catalog/pg_type.h" +#include "utils/builtins.h" +#include "utils/inval.h" + +PG_FUNCTION_INFO_V1(pgstatindex); +PG_FUNCTION_INFO_V1(bt_metap); +PG_FUNCTION_INFO_V1(bt_page_items); +PG_FUNCTION_INFO_V1(bt_page_stats); +PG_FUNCTION_INFO_V1(pg_relpages); + +extern Datum pgstatindex(PG_FUNCTION_ARGS); +extern Datum bt_metap(PG_FUNCTION_ARGS); +extern Datum bt_page_items(PG_FUNCTION_ARGS); +extern Datum bt_page_stats(PG_FUNCTION_ARGS); +extern Datum pg_relpages(PG_FUNCTION_ARGS); + +#define PGSTATINDEX_TYPE "public.pgstatindex_type" +#define PGSTATINDEX_NCOLUMNS 10 + +#define BTMETAP_TYPE "public.bt_metap_type" +#define BTMETAP_NCOLUMNS 6 + +#define BTPAGEITEMS_TYPE "public.bt_page_items_type" +#define BTPAGEITEMS_NCOLUMNS 6 + +#define BTPAGESTATS_TYPE "public.bt_page_stats_type" +#define BTPAGESTATS_NCOLUMNS 11 + + +#define IS_INDEX(r) ((r)->rd_rel->relkind == 'i') +#define IS_BTREE(r) ((r)->rd_rel->relam == BTREE_AM_OID) + +#define CHECK_PAGE_OFFSET_RANGE(page, offset) { \ + if ( !(FirstOffsetNumber<=(offset) && \ + (offset)<=PageGetMaxOffsetNumber(page)) ) \ +elog(ERROR, "Page offset number out of range."); } + +#define CHECK_RELATION_BLOCK_RANGE(rel, blkno) {
Re: [PATCHES] pgstattuple extension for indexes
Satoshi Nagayasu wrote: > Bruce, > > Attached patch has been cleaned up, > and modified to be able to work with CVS HEAD. I was thinking, isn't it a lot cleaner to define the functions to use OUT parameters instead of having to define a custom type for each? Also, in 8.2 there is a uninstall SQL script -- ISTM you should put the DROP commands there, not in the install script. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] pgstattuple extension for indexes
Alvaro Herrera <[EMAIL PROTECTED]> writes: > I was thinking, isn't it a lot cleaner to define the functions to use > OUT parameters instead of having to define a custom type for each? Not really --- it's a bit less notation maybe, but if he's got it written like that already, I see no need to change it. > Also, in 8.2 there is a uninstall SQL script -- ISTM you should put the > DROP commands there, not in the install script. Agreed. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] pgstattuple extension for indexes
BTIem is no longer in CVS HEAD, though it was in 8.1.X. Please update your patch for CVS HEAD. Thanks. --- Satoshi Nagayasu wrote: > Hi all, > > Here is a patch to add pgstatindex functions to the pgstattuple module, > which can work with 8.1.4. Please review and try it. Thanks. > > > Satoshi Nagayasu wrote: > > Bruce, > > > > I'll fix it in this week. Please wait a few days. > > Thanks. > > > > Bruce Momjian wrote: > >> nagayasu-san, > >> > >> This looks good, but we would like the code added to > >> /contrib/pgstattuple, rather than it being its own /contrib module. Can > >> you make that adjustment? Thanks. > >> > >> --- > >> > >> satoshi nagayasu wrote: > >>> Hi folks, > >>> > >>> As I said on -PATCHES, I've been working on an utility to get > >>> a b-tree index information. I'm happy to introduce > >>> my new functions to you. > >>> > >>> pgstattuple module provides a `pgstatindex()`, and other small > >>> functions, which allow you to get b-tree internal information. > >>> I believe this module will be helpful to know b-tree index deeply. > >>> > >>> So please try it, send comment to me, and have fun. > >>> > >>> Thanks, > >>> -- > >>> NAGAYASU Satoshi <[EMAIL PROTECTED]> > >>> > >>> - > >>> pgbench=# \x > >>> Expanded display is on. > >>> pgbench=# SELECT * FROM pgstatindex('accounts_pkey'); > >>> -[ RECORD 1 ]--+ > >>> version| 2 > >>> tree_level | 1 > >>> index_size | 3588096 > >>> root_block_no | 3 > >>> internal_pages | 0 > >>> leaf_pages | 437 > >>> empty_pages| 0 > >>> deleted_pages | 0 > >>> avg_leaf_density | 59.5 > >>> leaf_fragmentation | 49.89 > >>> - > >>> > >>> > >> [ application/x-gzip is not supported, skipping... ] > >> > >>> ---(end of broadcast)--- > >>> TIP 4: Have you searched our list archives? > >>> > >>>http://archives.postgresql.org > > > > > > > -- > NAGAYASU Satoshi <[EMAIL PROTECTED]> > Phone: +81-3-3523-8122 > diff -ruN pgstattuple.orig/Makefile pgstattuple/Makefile > --- pgstattuple.orig/Makefile 2006-08-10 19:22:47.0 +0900 > +++ pgstattuple/Makefile 2006-08-10 19:24:05.0 +0900 > @@ -6,7 +6,7 @@ > # > #- > > -SRCS = pgstattuple.c > +SRCS = pgstattuple.c pgstatindex.c > > MODULE_big = pgstattuple > OBJS = $(SRCS:.c=.o) > diff -ruN pgstattuple.orig/pgstatindex.c pgstattuple/pgstatindex.c > --- pgstattuple.orig/pgstatindex.c1970-01-01 09:00:00.0 +0900 > +++ pgstattuple/pgstatindex.c 2006-08-11 17:51:26.0 +0900 > @@ -0,0 +1,714 @@ > +/* > + * pgstatindex > + * > + * Copyright (c) 2006 Satoshi Nagayasu <[EMAIL PROTECTED]> > + * > + * Permission to use, copy, modify, and distribute this software and > + * its documentation for any purpose, without fee, and without a > + * written agreement is hereby granted, provided that the above > + * copyright notice and this paragraph and the following two > + * paragraphs appear in all copies. > + * > + * IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT, > + * INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING > + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS > + * DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED > + * OF THE POSSIBILITY OF SUCH DAMAGE. > + * > + * THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT > + * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR > + * A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS > + * IS" BASIS, AND THE AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, > + * SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. > + */ > + > +#include "postgres.h" > + > +#include "fmgr.h" > +#include "funcapi.h" > +#include "access/heapam.h" > +#include "access/itup.h" > +#include "access/nbtree.h" > +#include "access/transam.h" > +#include "catalog/namespace.h" > +#include "catalog/pg_type.h" > +#include "utils/builtins.h" > +#include "utils/inval.h" > + > +PG_FUNCTION_INFO_V1(pgstatindex); > +PG_FUNCTION_INFO_V1(bt_metap); > +PG_FUNCTION_INFO_V1(bt_page_items); > +PG_FUNCTION_INFO_V1(bt_page_stats); > +PG_FUNCTION_INFO_V1(pg_relpages); > + > +extern Datum pgstatindex(PG_FUNCTION_ARGS); > +extern Datum bt_metap(PG_FUNCTION_ARGS); > +extern Datum bt_page_items(PG_FUNCTION_ARGS); > +extern Datum bt_page_stats(PG_FUNCTION_ARGS); > +extern Datum pg_relpages(PG_FUNCTION_ARGS); > + > +#define PGSTATINDEX_TYPE "public.pgstatindex_type" > +#define PGSTATINDEX_NCOLUMNS 10 > + > +#define BTMETAP_TYPE "public.bt_metap_type" > +#define BTMETAP_NCOLUMNS 6 > + > +#
Re: [PATCHES] pgstattuple extension for indexes
Bruce, Bruce Momjian wrote: > BTIem is no longer in CVS HEAD, though it was in 8.1.X. Please update > your patch for CVS HEAD. Thanks. I've posted CVS HEAD workable version on Aug.14. Please check it out. Thanks. -- NAGAYASU Satoshi <[EMAIL PROTECTED]> Phone: +81-3-3523-8122 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] pgstattuple extension for indexes
Satoshi Nagayasu wrote: > Bruce, > > Bruce Momjian wrote: > > BTIem is no longer in CVS HEAD, though it was in 8.1.X. Please update > > your patch for CVS HEAD. Thanks. > > I've posted CVS HEAD workable version on Aug.14. > Please check it out. Thanks. OK, I found it, but it has no updates to README.pgstattuple to describe the new functionality. Should I write it? -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] pgstattuple extension for indexes
Sorry, I'll write README (and uninstall.sql?) by tomorrow. Bruce Momjian wrote: > Satoshi Nagayasu wrote: >> Bruce, >> >> Bruce Momjian wrote: >>> BTIem is no longer in CVS HEAD, though it was in 8.1.X. Please update >>> your patch for CVS HEAD. Thanks. >> I've posted CVS HEAD workable version on Aug.14. >> Please check it out. Thanks. > > OK, I found it, but it has no updates to README.pgstattuple to describe > the new functionality. Should I write it? > -- NAGAYASU Satoshi <[EMAIL PROTECTED]> Phone: +81-3-3523-8122 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] pgstattuple extension for indexes
Satoshi Nagayasu wrote: > Sorry, I'll write README (and uninstall.sql?) by tomorrow. Thanks. Yea, you need to update the uninstall too. --- > > Bruce Momjian wrote: > > Satoshi Nagayasu wrote: > >> Bruce, > >> > >> Bruce Momjian wrote: > >>> BTIem is no longer in CVS HEAD, though it was in 8.1.X. Please update > >>> your patch for CVS HEAD. Thanks. > >> I've posted CVS HEAD workable version on Aug.14. > >> Please check it out. Thanks. > > > > OK, I found it, but it has no updates to README.pgstattuple to describe > > the new functionality. Should I write it? > > > > > -- > NAGAYASU Satoshi <[EMAIL PROTECTED]> > Phone: +81-3-3523-8122 -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] pgstattuple extension for indexes
Patch applied. Thanks. I updated the README documentation for the new functions, attached. I could not update the Japanese version of the README. --- Satoshi Nagayasu wrote: > Bruce, > > Attached patch has been cleaned up, > and modified to be able to work with CVS HEAD. > > Thanks. > > Satoshi Nagayasu wrote: > > Alvaro, > > > > Alvaro Herrera wrote: > >> Huh, I bet it works with 8.1.4, but it doesn't work on CVS HEAD: > >> > >> /pgsql/source/00orig/contrib/pgstattuple/pgstatindex.c: In function > >> 'GetBTPageStatistics': > >> /pgsql/source/00orig/contrib/pgstattuple/pgstatindex.c:182: error: > >> 'BTItem' undeclared (first use in this function) > >> > >> > >> While you're at it, please consider removing C++ style comments and > >> unused code. > >> > >> Formatting is way off as well, but I guess that is easily fixed with > >> pgindent. > > > > Thanks for comments. I'm going to fix my patch from now. > > > >> Regarding the pg_relpages function, why do you think it's necessary? > >> (It returns the true number of blocks of a given relation). It may > >> belong into core given a reasonable use case, but otherwise it doesn't > >> seem to belong into pgstatindex (or pgstattuple for that matter). > > > > I wanted to sample some pages from the table/index, and get their statistics > > to know table/index conditions. I know pgstattuple() reports table > > statistics, however, pgstattuple() generates heavy CPU and I/O load. > > > > When we need to sample some pages from table/index, we need to know > > true number of blocks. > > > > I have another function, called pgstatpage(), to get information inside > > a single block/page statistics of the table. pg_relpages() will be used > > with this. > > > > Sorry for not mentioned in previous post about pgstatpage(), > > but I've remembered about it just now. > > > > Many memories in my brain have already `paged-out` (too busy in last few > > months), > > and some of them got `out-of-memory`. :^) > > > > Thanks. > > > -- > NAGAYASU Satoshi <[EMAIL PROTECTED]> > Phone: +81-3-3523-8122 > diff -ruN pgstattuple.orig/Makefile pgstattuple/Makefile > --- pgstattuple.orig/Makefile 2006-02-27 21:54:40.0 +0900 > +++ pgstattuple/Makefile 2006-08-14 09:28:58.0 +0900 > @@ -6,7 +6,7 @@ > # > #- > > -SRCS = pgstattuple.c > +SRCS = pgstattuple.c pgstatindex.c > > MODULE_big = pgstattuple > OBJS = $(SRCS:.c=.o) > diff -ruN pgstattuple.orig/pgstatindex.c pgstattuple/pgstatindex.c > --- pgstattuple.orig/pgstatindex.c1970-01-01 09:00:00.0 +0900 > +++ pgstattuple/pgstatindex.c 2006-08-14 11:24:23.0 +0900 > @@ -0,0 +1,706 @@ > +/* > + * pgstatindex > + * > + * Copyright (c) 2006 Satoshi Nagayasu <[EMAIL PROTECTED]> > + * > + * Permission to use, copy, modify, and distribute this software and > + * its documentation for any purpose, without fee, and without a > + * written agreement is hereby granted, provided that the above > + * copyright notice and this paragraph and the following two > + * paragraphs appear in all copies. > + * > + * IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT, > + * INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING > + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS > + * DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED > + * OF THE POSSIBILITY OF SUCH DAMAGE. > + * > + * THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT > + * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR > + * A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS > + * IS" BASIS, AND THE AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, > + * SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. > + */ > + > +#include "postgres.h" > + > +#include "fmgr.h" > +#include "funcapi.h" > +#include "access/heapam.h" > +#include "access/itup.h" > +#include "access/nbtree.h" > +#include "access/transam.h" > +#include "catalog/namespace.h" > +#include "catalog/pg_type.h" > +#include "utils/builtins.h" > +#include "utils/inval.h" > + > +PG_FUNCTION_INFO_V1(pgstatindex); > +PG_FUNCTION_INFO_V1(bt_metap); > +PG_FUNCTION_INFO_V1(bt_page_items); > +PG_FUNCTION_INFO_V1(bt_page_stats); > +PG_FUNCTION_INFO_V1(pg_relpages); > + > +extern Datum pgstatindex(PG_FUNCTION_ARGS); > +extern Datum bt_metap(PG_FUNCTION_ARGS); > +extern Datum bt_page_items(PG_FUNCTION_ARGS); > +extern Datum bt_page_stats(PG_FUNCTION_ARGS); > +extern Datum pg_relpages(PG_FUNCTION_ARGS); > + > +#define PGSTATINDEX_TYPE "public.pgstatindex_type" > +#define PGSTATINDEX_NCOLUMNS 10 > + > +#define BTMETAP_TYPE "public.bt_metap_type" > +#define BTMETAP_NCOLUMNS 6 > + > +#define BTPAGEITEMS_TYPE "public.bt_page_items_type" > +#define BTPAGEITEMS_NCOLUMNS 6 > + > +#define BTPAGESTATS_TYPE "pub
Re: [PATCHES] pgstattuple extension for indexes
Bruce Momjian <[EMAIL PROTECTED]> writes: > Patch applied. Thanks. For some reason I expected this patch to correct the portability errors and design problems identified here: http://archives.postgresql.org/pgsql-patches/2006-07/msg00100.php Not only has it not fixed anything, it's made things worse: gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -g -fpic -I. -I../../src/include -D_GNU_SOURCE -c -o pgstattuple.o pgstattuple.c pgstattuple.c: In function 'pgstat_btree': pgstattuple.c:335: warning: format '%llu' expects type 'long long unsigned int', but argument 2 has type 'uint64' pgstattuple.c:335: warning: format '%llu' expects type 'long long unsigned int', but argument 3 has type 'uint64' pgstattuple.c:335: warning: format '%llu' expects type 'long long unsigned int', but argument 4 has type 'uint64' gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -g -fpic -I. -I../../src/include -D_GNU_SOURCE -c -o pgstatindex.o pgstatindex.c pgstatindex.c: In function 'bt_page_items': pgstatindex.c:564: warning: format '%d' expects type 'int', but argument 4 has type 'long unsigned int' pgstatindex.c:564: warning: format '%d' expects type 'int', but argument 4 has type 'long unsigned int' ar crs libpgstattuple.a pgstattuple.o pgstatindex.o The only reason the buildfarm isn't crashing on this contrib module is that it lacks any regression test to crash on. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] pgstattuple extension for indexes
Thanks Bruce, Here are updated Japanese README, and uninstall_pgstattuple.sql. Bruce Momjian wrote: > Patch applied. Thanks. > > I updated the README documentation for the new functions, attached. I > could not update the Japanese version of the README. > > --- > > > Satoshi Nagayasu wrote: >> Bruce, >> >> Attached patch has been cleaned up, >> and modified to be able to work with CVS HEAD. >> >> Thanks. >> >> Satoshi Nagayasu wrote: >>> Alvaro, >>> >>> Alvaro Herrera wrote: Huh, I bet it works with 8.1.4, but it doesn't work on CVS HEAD: /pgsql/source/00orig/contrib/pgstattuple/pgstatindex.c: In function 'GetBTPageStatistics': /pgsql/source/00orig/contrib/pgstattuple/pgstatindex.c:182: error: 'BTItem' undeclared (first use in this function) While you're at it, please consider removing C++ style comments and unused code. Formatting is way off as well, but I guess that is easily fixed with pgindent. >>> Thanks for comments. I'm going to fix my patch from now. >>> Regarding the pg_relpages function, why do you think it's necessary? (It returns the true number of blocks of a given relation). It may belong into core given a reasonable use case, but otherwise it doesn't seem to belong into pgstatindex (or pgstattuple for that matter). >>> I wanted to sample some pages from the table/index, and get their statistics >>> to know table/index conditions. I know pgstattuple() reports table >>> statistics, however, pgstattuple() generates heavy CPU and I/O load. >>> >>> When we need to sample some pages from table/index, we need to know >>> true number of blocks. >>> >>> I have another function, called pgstatpage(), to get information inside >>> a single block/page statistics of the table. pg_relpages() will be used >>> with this. >>> >>> Sorry for not mentioned in previous post about pgstatpage(), >>> but I've remembered about it just now. >>> >>> Many memories in my brain have already `paged-out` (too busy in last few >>> months), >>> and some of them got `out-of-memory`. :^) >>> >>> Thanks. >> >> -- >> NAGAYASU Satoshi <[EMAIL PROTECTED]> >> Phone: +81-3-3523-8122 > >> diff -ruN pgstattuple.orig/Makefile pgstattuple/Makefile >> --- pgstattuple.orig/Makefile2006-02-27 21:54:40.0 +0900 >> +++ pgstattuple/Makefile 2006-08-14 09:28:58.0 +0900 >> @@ -6,7 +6,7 @@ >> # >> #- >> >> -SRCS= pgstattuple.c >> +SRCS= pgstattuple.c pgstatindex.c >> >> MODULE_big = pgstattuple >> OBJS= $(SRCS:.c=.o) >> diff -ruN pgstattuple.orig/pgstatindex.c pgstattuple/pgstatindex.c >> --- pgstattuple.orig/pgstatindex.c 1970-01-01 09:00:00.0 +0900 >> +++ pgstattuple/pgstatindex.c2006-08-14 11:24:23.0 +0900 >> @@ -0,0 +1,706 @@ >> +/* >> + * pgstatindex >> + * >> + * Copyright (c) 2006 Satoshi Nagayasu <[EMAIL PROTECTED]> >> + * >> + * Permission to use, copy, modify, and distribute this software and >> + * its documentation for any purpose, without fee, and without a >> + * written agreement is hereby granted, provided that the above >> + * copyright notice and this paragraph and the following two >> + * paragraphs appear in all copies. >> + * >> + * IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT, >> + * INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING >> + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS >> + * DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED >> + * OF THE POSSIBILITY OF SUCH DAMAGE. >> + * >> + * THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT >> + * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR >> + * A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS >> + * IS" BASIS, AND THE AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, >> + * SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. >> + */ >> + >> +#include "postgres.h" >> + >> +#include "fmgr.h" >> +#include "funcapi.h" >> +#include "access/heapam.h" >> +#include "access/itup.h" >> +#include "access/nbtree.h" >> +#include "access/transam.h" >> +#include "catalog/namespace.h" >> +#include "catalog/pg_type.h" >> +#include "utils/builtins.h" >> +#include "utils/inval.h" >> + >> +PG_FUNCTION_INFO_V1(pgstatindex); >> +PG_FUNCTION_INFO_V1(bt_metap); >> +PG_FUNCTION_INFO_V1(bt_page_items); >> +PG_FUNCTION_INFO_V1(bt_page_stats); >> +PG_FUNCTION_INFO_V1(pg_relpages); >> + >> +extern Datum pgstatindex(PG_FUNCTION_ARGS); >> +extern Datum bt_metap(PG_FUNCTION_ARGS); >> +extern Datum bt_page_items(PG_FUNCTION_ARGS); >> +extern Datum bt_page_stats(PG_FUNCTION_ARGS); >> +extern Datum pg_relpages(PG_FUNCTION_ARGS); >> + >> +#define PGSTATINDEX_TYPE "public.pgstatindex_type" >> +#define PGSTATINDEX_NCOLUMNS 10 >>
Re: [PATCHES] pgstattuple extension for indexes
Tom Lane wrote: > gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline > -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -g -fpic > -I. -I../../src/include -D_GNU_SOURCE -c -o pgstatindex.o pgstatindex.c > pgstatindex.c: In function 'bt_page_items': > pgstatindex.c:564: warning: format '%d' expects type 'int', but argument 4 > has type 'long unsigned int' > pgstatindex.c:564: warning: format '%d' expects type 'int', but argument 4 > has type 'long unsigned int' I guess my '%d' should be '%zd', right? -- NAGAYASU Satoshi <[EMAIL PROTECTED]> Phone: +81-3-3523-8122 *** pgstatindex.c 2006-09-03 02:05:29.0 +0900 --- pgstatindex.c.new 2006-09-04 08:22:42.0 +0900 *** *** 561,567 values[j] = palloc(32); snprintf(values[j++], 32, "(%u,%u)", blkno, itup->t_tid.ip_posid); values[j] = palloc(32); ! snprintf(values[j++], 32, "%d", IndexTupleSize(itup)); values[j] = palloc(32); snprintf(values[j++], 32, "%c", IndexTupleHasNulls(itup) ? 't' : 'f'); values[j] = palloc(32); --- 561,567 values[j] = palloc(32); snprintf(values[j++], 32, "(%u,%u)", blkno, itup->t_tid.ip_posid); values[j] = palloc(32); ! snprintf(values[j++], 32, "%zd", IndexTupleSize(itup)); values[j] = palloc(32); snprintf(values[j++], 32, "%c", IndexTupleHasNulls(itup) ? 't' : 'f'); values[j] = palloc(32); ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] pgstattuple extension for indexes
Bruce Momjian <[EMAIL PROTECTED]> wrote: > Patch applied. Thanks. The two attached patches fix contrib/pgstattuple. pgstattuple.c.diff removes the fragmemtation reporting in pgstattuple(). It is no longer needed, because pgstatindex() has upward functionality now. Also, the report using elog was judged as improper in earlier discusses. pgstattuple.sql.in.diff removes DROP statements in the installer. The DROP statements make some unpleasant ERROR logs during install. According to other contrib modules, DROPs should be in the uninstaller and should not be in the installer. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center pgstattuple.sql.in.diff Description: Binary data pgstattuple.c.diff Description: Binary data ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] pgstattuple extension for indexes
ITAGAKI Takahiro <[EMAIL PROTECTED]> writes: > The two attached patches fix contrib/pgstattuple. Good, applied. I made some additional changes to get install/uninstall/ reinstall to work cleanly after the latest additions, and to get it to compile without warnings on a 64-bit Fedora machine. (It seems to actually work there, too.) I notice that the original pgstattuple() function comes in two flavors, one with OID input and one with text-relation-name input. Shouldn't all the others be likewise? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] pgstattuple extension for indexes
Satoshi Nagayasu <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> pgstatindex.c: In function 'bt_page_items': >> pgstatindex.c:564: warning: format '%d' expects type 'int', but argument 4 >> has type 'long unsigned int' > I guess my '%d' should be '%zd', right? No, that sounds even less portable :-( Given the expected range of IndexTupleSize(), it seems sufficient to cast its result to int and then use %d formatting. I've done that in the latest commit. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] pgstattuple extension for indexes
Applied. --- Satoshi Nagayasu wrote: > Thanks Bruce, > > Here are updated Japanese README, and uninstall_pgstattuple.sql. > > Bruce Momjian wrote: > > Patch applied. Thanks. > > > > I updated the README documentation for the new functions, attached. I > > could not update the Japanese version of the README. > > > > --- > > > > > > Satoshi Nagayasu wrote: > >> Bruce, > >> > >> Attached patch has been cleaned up, > >> and modified to be able to work with CVS HEAD. > >> > >> Thanks. > >> > >> Satoshi Nagayasu wrote: > >>> Alvaro, > >>> > >>> Alvaro Herrera wrote: > Huh, I bet it works with 8.1.4, but it doesn't work on CVS HEAD: > > /pgsql/source/00orig/contrib/pgstattuple/pgstatindex.c: In function > 'GetBTPageStatistics': > /pgsql/source/00orig/contrib/pgstattuple/pgstatindex.c:182: error: > 'BTItem' undeclared (first use in this function) > > > While you're at it, please consider removing C++ style comments and > unused code. > > Formatting is way off as well, but I guess that is easily fixed with > pgindent. > >>> Thanks for comments. I'm going to fix my patch from now. > >>> > Regarding the pg_relpages function, why do you think it's necessary? > (It returns the true number of blocks of a given relation). It may > belong into core given a reasonable use case, but otherwise it doesn't > seem to belong into pgstatindex (or pgstattuple for that matter). > >>> I wanted to sample some pages from the table/index, and get their > >>> statistics > >>> to know table/index conditions. I know pgstattuple() reports table > >>> statistics, however, pgstattuple() generates heavy CPU and I/O load. > >>> > >>> When we need to sample some pages from table/index, we need to know > >>> true number of blocks. > >>> > >>> I have another function, called pgstatpage(), to get information inside > >>> a single block/page statistics of the table. pg_relpages() will be used > >>> with this. > >>> > >>> Sorry for not mentioned in previous post about pgstatpage(), > >>> but I've remembered about it just now. > >>> > >>> Many memories in my brain have already `paged-out` (too busy in last few > >>> months), > >>> and some of them got `out-of-memory`. :^) > >>> > >>> Thanks. > >> > >> -- > >> NAGAYASU Satoshi <[EMAIL PROTECTED]> > >> Phone: +81-3-3523-8122 > > > >> diff -ruN pgstattuple.orig/Makefile pgstattuple/Makefile > >> --- pgstattuple.orig/Makefile 2006-02-27 21:54:40.0 +0900 > >> +++ pgstattuple/Makefile 2006-08-14 09:28:58.0 +0900 > >> @@ -6,7 +6,7 @@ > >> # > >> #- > >> > >> -SRCS = pgstattuple.c > >> +SRCS = pgstattuple.c pgstatindex.c > >> > >> MODULE_big= pgstattuple > >> OBJS = $(SRCS:.c=.o) > >> diff -ruN pgstattuple.orig/pgstatindex.c pgstattuple/pgstatindex.c > >> --- pgstattuple.orig/pgstatindex.c 1970-01-01 09:00:00.0 +0900 > >> +++ pgstattuple/pgstatindex.c 2006-08-14 11:24:23.0 +0900 > >> @@ -0,0 +1,706 @@ > >> +/* > >> + * pgstatindex > >> + * > >> + * Copyright (c) 2006 Satoshi Nagayasu <[EMAIL PROTECTED]> > >> + * > >> + * Permission to use, copy, modify, and distribute this software and > >> + * its documentation for any purpose, without fee, and without a > >> + * written agreement is hereby granted, provided that the above > >> + * copyright notice and this paragraph and the following two > >> + * paragraphs appear in all copies. > >> + * > >> + * IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT, > >> + * INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING > >> + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS > >> + * DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED > >> + * OF THE POSSIBILITY OF SUCH DAMAGE. > >> + * > >> + * THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT > >> + * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR > >> + * A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS > >> + * IS" BASIS, AND THE AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, > >> + * SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. > >> + */ > >> + > >> +#include "postgres.h" > >> + > >> +#include "fmgr.h" > >> +#include "funcapi.h" > >> +#include "access/heapam.h" > >> +#include "access/itup.h" > >> +#include "access/nbtree.h" > >> +#include "access/transam.h" > >> +#include "catalog/namespace.h" > >> +#include "catalog/pg_type.h" > >> +#include "utils/builtins.h" > >> +#include "utils/inval.h" > >> + > >> +PG_FUNCTION_INFO_V1(pgstatindex); > >> +PG_FUNCTION_INFO_V1(bt_metap); > >> +PG_FUNCTION_INFO_V1(bt_page_items); > >> +PG_FUNCTION_INFO_V1(bt_page_stats); > >> +PG_FUNCTION_INFO
Re: [HACKERS] [PATCHES] pgstattuple extension for indexes
Bruce Momjian wrote: > > I thought these new functions were going to be merged into > /contrib/pgstattuple. Well, that's exactly what this patch seems to do ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] pgstattuple extension for indexes
Alvaro Herrera wrote: > Bruce Momjian wrote: > > > > I thought these new functions were going to be merged into > > /contrib/pgstattuple. > > Well, that's exactly what this patch seems to do ... Well, looking at the tarball it puts everything in pgstatindex, and the Makefile is: #- # # pgstatindex Makefile # # $PostgreSQL$ # #- SRCS= pgstatindex.c MODULE_big = pgstatindex OBJS= $(SRCS:.c=.o) DOCS= DATA_built = pgstatindex.sql ifdef USE_PGXS PGXS := $(shell pg_config --pgxs) include $(PGXS) else subdir = contrib/pgstatindex top_builddir = /home/snaga/pgsql/sources/postgresql-8.1.3 include $(top_builddir)/src/Makefile.global include $(top_srcdir)/contrib/contrib-global.mk endif -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend