Re: [HACKERS] pg_freespacemap question
Patch applied. Thanks.
---
Mark Kirkwood wrote:
> Tom Lane wrote:
> > Mark Kirkwood <[EMAIL PROTECTED]> writes:
> >
> >>>Good points! I had not noticed this test case. Probably NULL is better
> >
> >
> >>Would setting it to 'BLCKSZ - (fixed index header stuff)' be better,
> >
> >
> > No, I don't think so, because that will just make it harder to recognize
> > what's what (remember that BLCKSZ isn't really a constant, and the index
> > overhead is not the same for all AMs either). The point here is that
> > for indexes the FSM tracks whole-page availability, not the amount of
> > free space within pages. So I think NULL is a reasonable representation
> > of that. Using NULL will make it easy to filter the results if you want
> > to see only heap-page data or only index-page data, whereas it will be
> > very hard to do that if the view adopts an ultimately-artificial
> > convention about the amount of available space on an index page.
> >
>
> Right - after suggesting it I realized that coding the different index
> overhead for each possible AM would have been ... difficult :-). A patch
> is attached to implement the NULL free bytes and other recommendations:
>
> 1/ Index free bytes set to NULL
> 2/ Comment added to the README briefly mentioning the index business
> 3/ Columns reordered more logically
> 4/ 'Blockid' column removed
> 5/ Free bytes column renamed to just 'bytes' instead of 'blockfreebytes'
>
> Now 5/ was only hinted at, but seemed worth doing while I was there
> (hopefully I haven't made it too terse now).
>
> cheers
>
> Mark
>
>
> Index: pg_freespacemap.c
> ===
> RCS file: /projects/cvsroot/pgsql/contrib/pg_freespacemap/pg_freespacemap.c,v
> retrieving revision 1.2
> diff -c -r1.2 pg_freespacemap.c
> *** pg_freespacemap.c 14 Feb 2006 15:03:59 - 1.2
> --- pg_freespacemap.c 9 Mar 2006 03:38:10 -
> ***
> *** 12,18
> #include "storage/freespace.h"
> #include "utils/relcache.h"
>
> ! #define NUM_FREESPACE_PAGES_ELEM6
>
> #if defined(WIN32) || defined(__CYGWIN__)
> /* Need DLLIMPORT for some things that are not so marked in main headers */
> --- 12,18
> #include "storage/freespace.h"
> #include "utils/relcache.h"
>
> ! #define NUM_FREESPACE_PAGES_ELEM5
>
> #if defined(WIN32) || defined(__CYGWIN__)
> /* Need DLLIMPORT for some things that are not so marked in main headers */
> ***
> *** 29,40
> typedef struct
> {
>
> - uint32 blockid;
> - uint32 relfilenode;
> uint32 reltablespace;
> uint32 reldatabase;
> uint32 relblocknumber;
> ! uint32 blockfreebytes;
>
> } FreeSpacePagesRec;
>
> --- 29,40
> typedef struct
> {
>
> uint32 reltablespace;
> uint32 reldatabase;
> + uint32 relfilenode;
> uint32 relblocknumber;
> ! uint32 bytes;
> ! boolisindex;
>
> } FreeSpacePagesRec;
>
> ***
> *** 91,107
>
> /* Construct a tuple to return. */
> tupledesc = CreateTemplateTupleDesc(NUM_FREESPACE_PAGES_ELEM,
> false);
> ! TupleDescInitEntry(tupledesc, (AttrNumber) 1, "blockid",
> !INT4OID, -1, 0);
> ! TupleDescInitEntry(tupledesc, (AttrNumber) 2, "relfilenode",
> OIDOID, -1, 0);
> ! TupleDescInitEntry(tupledesc, (AttrNumber) 3, "reltablespace",
> OIDOID, -1, 0);
> ! TupleDescInitEntry(tupledesc, (AttrNumber) 4, "reldatabase",
> OIDOID, -1, 0);
> ! TupleDescInitEntry(tupledesc, (AttrNumber) 5, "relblocknumber",
> INT8OID, -1, 0);
> ! TupleDescInitEntry(tupledesc, (AttrNumber) 6, "blockfreebytes",
> INT4OID, -1, 0);
>
> /* Generate attribute metadata needed later to produce tuples */
> --- 91,105
>
> /* Construct a tuple to return. */
> tupledesc = CreateTemplateTupleDesc(NUM_FREESPACE_PAGES_ELEM,
> false);
> ! TupleDescInitEntry(tupledesc, (AttrNumber) 1, "reltablespace",
> OIDOID, -1, 0);
> ! TupleDescInitEntry(tupledesc, (AttrNumber) 2, "reldatabase",
> OIDOID, -1, 0);
Re: [HACKERS] pg_freespacemap question
Mark, I have tried your patches and it worked great. Thanks. -- Tatsuo Ishii SRA OSS, Inc. Japan > Tom Lane wrote: > > Mark Kirkwood <[EMAIL PROTECTED]> writes: > > > >>>Good points! I had not noticed this test case. Probably NULL is better > > > > > >>Would setting it to 'BLCKSZ - (fixed index header stuff)' be better, > > > > > > No, I don't think so, because that will just make it harder to recognize > > what's what (remember that BLCKSZ isn't really a constant, and the index > > overhead is not the same for all AMs either). The point here is that > > for indexes the FSM tracks whole-page availability, not the amount of > > free space within pages. So I think NULL is a reasonable representation > > of that. Using NULL will make it easy to filter the results if you want > > to see only heap-page data or only index-page data, whereas it will be > > very hard to do that if the view adopts an ultimately-artificial > > convention about the amount of available space on an index page. > > > > Right - after suggesting it I realized that coding the different index > overhead for each possible AM would have been ... difficult :-). A patch > is attached to implement the NULL free bytes and other recommendations: > > 1/ Index free bytes set to NULL > 2/ Comment added to the README briefly mentioning the index business > 3/ Columns reordered more logically > 4/ 'Blockid' column removed > 5/ Free bytes column renamed to just 'bytes' instead of 'blockfreebytes' > > Now 5/ was only hinted at, but seemed worth doing while I was there > (hopefully I haven't made it too terse now). > > cheers > > Mark > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_freespacemap question
Tom Lane wrote:
Mark Kirkwood <[EMAIL PROTECTED]> writes:
Good points! I had not noticed this test case. Probably NULL is better
Would setting it to 'BLCKSZ - (fixed index header stuff)' be better,
No, I don't think so, because that will just make it harder to recognize
what's what (remember that BLCKSZ isn't really a constant, and the index
overhead is not the same for all AMs either). The point here is that
for indexes the FSM tracks whole-page availability, not the amount of
free space within pages. So I think NULL is a reasonable representation
of that. Using NULL will make it easy to filter the results if you want
to see only heap-page data or only index-page data, whereas it will be
very hard to do that if the view adopts an ultimately-artificial
convention about the amount of available space on an index page.
Right - after suggesting it I realized that coding the different index
overhead for each possible AM would have been ... difficult :-). A patch
is attached to implement the NULL free bytes and other recommendations:
1/ Index free bytes set to NULL
2/ Comment added to the README briefly mentioning the index business
3/ Columns reordered more logically
4/ 'Blockid' column removed
5/ Free bytes column renamed to just 'bytes' instead of 'blockfreebytes'
Now 5/ was only hinted at, but seemed worth doing while I was there
(hopefully I haven't made it too terse now).
cheers
Mark
Index: pg_freespacemap.c
===
RCS file: /projects/cvsroot/pgsql/contrib/pg_freespacemap/pg_freespacemap.c,v
retrieving revision 1.2
diff -c -r1.2 pg_freespacemap.c
*** pg_freespacemap.c 14 Feb 2006 15:03:59 - 1.2
--- pg_freespacemap.c 9 Mar 2006 03:38:10 -
***
*** 12,18
#include "storage/freespace.h"
#include "utils/relcache.h"
! #define NUM_FREESPACE_PAGES_ELEM6
#if defined(WIN32) || defined(__CYGWIN__)
/* Need DLLIMPORT for some things that are not so marked in main headers */
--- 12,18
#include "storage/freespace.h"
#include "utils/relcache.h"
! #define NUM_FREESPACE_PAGES_ELEM5
#if defined(WIN32) || defined(__CYGWIN__)
/* Need DLLIMPORT for some things that are not so marked in main headers */
***
*** 29,40
typedef struct
{
- uint32 blockid;
- uint32 relfilenode;
uint32 reltablespace;
uint32 reldatabase;
uint32 relblocknumber;
! uint32 blockfreebytes;
} FreeSpacePagesRec;
--- 29,40
typedef struct
{
uint32 reltablespace;
uint32 reldatabase;
+ uint32 relfilenode;
uint32 relblocknumber;
! uint32 bytes;
! boolisindex;
} FreeSpacePagesRec;
***
*** 91,107
/* Construct a tuple to return. */
tupledesc = CreateTemplateTupleDesc(NUM_FREESPACE_PAGES_ELEM,
false);
! TupleDescInitEntry(tupledesc, (AttrNumber) 1, "blockid",
! INT4OID, -1, 0);
! TupleDescInitEntry(tupledesc, (AttrNumber) 2, "relfilenode",
OIDOID, -1, 0);
! TupleDescInitEntry(tupledesc, (AttrNumber) 3, "reltablespace",
OIDOID, -1, 0);
! TupleDescInitEntry(tupledesc, (AttrNumber) 4, "reldatabase",
OIDOID, -1, 0);
! TupleDescInitEntry(tupledesc, (AttrNumber) 5, "relblocknumber",
INT8OID, -1, 0);
! TupleDescInitEntry(tupledesc, (AttrNumber) 6, "blockfreebytes",
INT4OID, -1, 0);
/* Generate attribute metadata needed later to produce tuples */
--- 91,105
/* Construct a tuple to return. */
tupledesc = CreateTemplateTupleDesc(NUM_FREESPACE_PAGES_ELEM,
false);
! TupleDescInitEntry(tupledesc, (AttrNumber) 1, "reltablespace",
OIDOID, -1, 0);
! TupleDescInitEntry(tupledesc, (AttrNumber) 2, "reldatabase",
OIDOID, -1, 0);
! TupleDescInitEntry(tupledesc, (AttrNumber) 3, "relfilenode",
OIDOID, -1, 0);
! TupleDescInitEntry(tupledesc, (AttrNumber) 4, "relblocknumber",
INT8OID, -1, 0);
! TupleD
Re: [HACKERS] pg_freespacemap question
Mark Kirkwood <[EMAIL PROTECTED]> writes: >> Good points! I had not noticed this test case. Probably NULL is better > Would setting it to 'BLCKSZ - (fixed index header stuff)' be better, No, I don't think so, because that will just make it harder to recognize what's what (remember that BLCKSZ isn't really a constant, and the index overhead is not the same for all AMs either). The point here is that for indexes the FSM tracks whole-page availability, not the amount of free space within pages. So I think NULL is a reasonable representation of that. Using NULL will make it easy to filter the results if you want to see only heap-page data or only index-page data, whereas it will be very hard to do that if the view adopts an ultimately-artificial convention about the amount of available space on an index page. regards, tom lane ---(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: [HACKERS] pg_freespacemap question
Mark Kirkwood wrote: Tatsuo Ishii wrote: BTW, I found the answer to my question myself by reading the source code: if that's an index, then blockfreebytes is explicitly set to 0. I suggest that this should be noted in the README and in this case blockfreebytes is better to set to NULL, rather than 0. Good points! I had not noticed this test case. Probably NULL is better than zero. Would setting it to 'BLCKSZ - (fixed index header stuff)' be better, since the btree page is empty? (I'll have to read up on how to calculate the header stuff!). regards Mark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_freespacemap question
Tatsuo Ishii wrote: > Just for curiousity, why FSM gathers info for indexes? I thought FSM > is only good for tables. It's part of the implementation of the page-recycling algorithm for btrees Tom did for 7.4. When a btree page is empty after a vacuum, it's entered in the free space map. When a page is split, the new page is taken from the FSM (or the relation is extended if there isn't any.) That's why the bytes-free number is zero: when a btree page makes it into the FSM, we are sure it's completely empty. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pg_freespacemap question
> > BTW, I found the answer to my question myself by reading the source > > code: if that's an index, then blockfreebytes is explicitly set to 0. > > I suggest that this should be noted in the README and in this case > > blockfreebytes is better to set to NULL, rather than 0. > > > > Good points! I had not noticed this test case. Probably NULL is better > than zero. Just for curiousity, why FSM gathers info for indexes? I thought FSM is only good for tables. -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_freespacemap question
Tom Lane wrote: Tatsuo Ishii <[EMAIL PROTECTED]> writes: Peter Eisentraut wrote: I have another question -- why is the view showing relfilenode and reltablespace? I imagine it should be showing the relation Oid instead. I guess that's because FSM keeps those info, not relation oid. Right, which is correct because free space is associated with physical files not logical relations. (TRUNCATE, CLUSTER, etc will completely change the freespace situation for a rel, but they don't change its OID.) I do agree with the comment that the column order seems nonintuitive; I'd expect database/tablespace/relfilenode/blocknumber, or possibly tablespace first. The names used for the columns could do with reconsideration. And I don't see the point of the blockid column at all. Tom - agreed, I'll look at making these changes too! Cheers Mark ---(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: [HACKERS] pg_freespacemap question
Tatsuo Ishii wrote: Peter Eisentraut wrote: Am Dienstag, 7. März 2006 15:09 schrieb Tatsuo Ishii: test=# select * from pg_freespacemap where blockfreebytes = 0; blockid | relfilenode | reltablespace | reldatabase | relblocknumber | blockfreebytes -+-+---+-++ 25 |2619 | 1663 | 16403 | 0 | 0 63 |2619 | 1663 | 16384 | 10 | 0 (2 rows) I've never heard of this thing before but is this column order supposed to make sense? I have another question -- why is the view showing relfilenode and reltablespace? I imagine it should be showing the relation Oid instead. I guess that's because FSM keeps those info, not relation oid. And what is this "blockid" thing? from README.pg_freespacemap: blockid| | Id, 1.. max_fsm_pages I put that in as a bit of a sanity check - to see if the view was picking up all the fsm pages - guess it is a bit redundant now. BTW, I found the answer to my question myself by reading the source code: if that's an index, then blockfreebytes is explicitly set to 0. I suggest that this should be noted in the README and in this case blockfreebytes is better to set to NULL, rather than 0. Good points! I had not noticed this test case. Probably NULL is better than zero. I'll look into making these changes! (good to see people checking the view out). Cheers Mark ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_freespacemap question
Tatsuo Ishii <[EMAIL PROTECTED]> writes: >> Peter Eisentraut wrote: >> I have another question -- why is the view showing relfilenode and >> reltablespace? I imagine it should be showing the relation Oid instead. > I guess that's because FSM keeps those info, not relation oid. Right, which is correct because free space is associated with physical files not logical relations. (TRUNCATE, CLUSTER, etc will completely change the freespace situation for a rel, but they don't change its OID.) I do agree with the comment that the column order seems nonintuitive; I'd expect database/tablespace/relfilenode/blocknumber, or possibly tablespace first. The names used for the columns could do with reconsideration. And I don't see the point of the blockid column at all. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_freespacemap question
> Peter Eisentraut wrote: > > Am Dienstag, 7. März 2006 15:09 schrieb Tatsuo Ishii: > > > test=# select * from pg_freespacemap where blockfreebytes = 0; > > > blockid | relfilenode | reltablespace | reldatabase | relblocknumber | > > > blockfreebytes > > > -+-+---+-++ > > > > > > 25 |2619 | 1663 | 16403 | 0 | > > >0 > > > 63 |2619 | 1663 | 16384 | 10 | > > >0 > > > (2 rows) > > > > I've never heard of this thing before but is this column order supposed to > > make sense? > > I have another question -- why is the view showing relfilenode and > reltablespace? I imagine it should be showing the relation Oid instead. I guess that's because FSM keeps those info, not relation oid. > And what is this "blockid" thing? from README.pg_freespacemap: blockid| | Id, 1.. max_fsm_pages BTW, I found the answer to my question myself by reading the source code: if that's an index, then blockfreebytes is explicitly set to 0. I suggest that this should be noted in the README and in this case blockfreebytes is better to set to NULL, rather than 0. -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_freespacemap question
Peter Eisentraut wrote: > Am Dienstag, 7. März 2006 15:09 schrieb Tatsuo Ishii: > > test=# select * from pg_freespacemap where blockfreebytes = 0; > > blockid | relfilenode | reltablespace | reldatabase | relblocknumber | > > blockfreebytes > > -+-+---+-++ > > > > 25 |2619 | 1663 | 16403 | 0 | > > 0 > > 63 |2619 | 1663 | 16384 | 10 | > > 0 > > (2 rows) > > I've never heard of this thing before but is this column order supposed to > make sense? I have another question -- why is the view showing relfilenode and reltablespace? I imagine it should be showing the relation Oid instead. And what is this "blockid" thing? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_freespacemap question
Am Dienstag, 7. März 2006 15:09 schrieb Tatsuo Ishii: > test=# select * from pg_freespacemap where blockfreebytes = 0; > blockid | relfilenode | reltablespace | reldatabase | relblocknumber | > blockfreebytes > -+-+---+-++ > > 25 |2619 | 1663 | 16403 | 0 | >0 > 63 |2619 | 1663 | 16384 | 10 | >0 > (2 rows) I've never heard of this thing before but is this column order supposed to make sense? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
