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 -0000 1.2
--- pg_freespacemap.c 9 Mar 2006 03:38:10 -0000
***************
*** 12,18 ****
#include "storage/freespace.h"
#include "utils/relcache.h"
! #define NUM_FREESPACE_PAGES_ELEM 6
#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_ELEM 5
#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;
! bool isindex;
} 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);
! TupleDescInitEntry(tupledesc, (AttrNumber) 5, "bytes",
INT4OID, -1, 0);
/* Generate attribute metadata needed later to produce tuples */
***************
*** 129,135 ****
fctx->values[2] = (char *) palloc(3 * sizeof(uint32) + 1);
fctx->values[3] = (char *) palloc(3 * sizeof(uint32) + 1);
fctx->values[4] = (char *) palloc(3 * sizeof(uint32) + 1);
- fctx->values[5] = (char *) palloc(3 * sizeof(uint32) + 1);
/* Return to original context when allocating transient memory
*/
--- 127,132 ----
***************
*** 158,169 ****
for (nPages = 0; nPages < fsmrel->storedPages;
nPages++)
{
- fctx->record[i].blockid = i;
- fctx->record[i].relfilenode =
fsmrel->key.relNode;
fctx->record[i].reltablespace =
fsmrel->key.spcNode;
fctx->record[i].reldatabase =
fsmrel->key.dbNode;
fctx->record[i].relblocknumber =
IndexFSMPageGetPageNum(page);
! fctx->record[i].blockfreebytes = 0;
/* index.*/
page++;
i++;
--- 155,166 ----
for (nPages = 0; nPages < fsmrel->storedPages;
nPages++)
{
fctx->record[i].reltablespace =
fsmrel->key.spcNode;
fctx->record[i].reldatabase =
fsmrel->key.dbNode;
+ fctx->record[i].relfilenode =
fsmrel->key.relNode;
fctx->record[i].relblocknumber =
IndexFSMPageGetPageNum(page);
! fctx->record[i].bytes = 0;
! fctx->record[i].isindex = true;
page++;
i++;
***************
*** 178,189 ****
for (nPages = 0; nPages < fsmrel->storedPages;
nPages++)
{
- fctx->record[i].blockid = i;
- fctx->record[i].relfilenode =
fsmrel->key.relNode;
fctx->record[i].reltablespace =
fsmrel->key.spcNode;
fctx->record[i].reldatabase =
fsmrel->key.dbNode;
fctx->record[i].relblocknumber =
FSMPageGetPageNum(page);
! fctx->record[i].blockfreebytes =
FSMPageGetSpace(page);
page++;
i++;
--- 175,186 ----
for (nPages = 0; nPages < fsmrel->storedPages;
nPages++)
{
fctx->record[i].reltablespace =
fsmrel->key.spcNode;
fctx->record[i].reldatabase =
fsmrel->key.dbNode;
+ fctx->record[i].relfilenode =
fsmrel->key.relNode;
fctx->record[i].relblocknumber =
FSMPageGetPageNum(page);
! fctx->record[i].bytes =
FSMPageGetSpace(page);
! fctx->record[i].isindex = false;
page++;
i++;
***************
*** 209,227 ****
if (funcctx->call_cntr < funcctx->max_calls)
{
uint32 i = funcctx->call_cntr;
- sprintf(fctx->values[0], "%u", fctx->record[i].blockid);
- sprintf(fctx->values[1], "%u", fctx->record[i].relfilenode);
- sprintf(fctx->values[2], "%u", fctx->record[i].reltablespace);
- sprintf(fctx->values[3], "%u", fctx->record[i].reldatabase);
- sprintf(fctx->values[4], "%u", fctx->record[i].relblocknumber);
- sprintf(fctx->values[5], "%u", fctx->record[i].blockfreebytes);
/* Build and return the tuple. */
! tuple = BuildTupleFromCStrings(funcctx->attinmeta,
fctx->values);
result = HeapTupleGetDatum(tuple);
--- 206,246 ----
if (funcctx->call_cntr < funcctx->max_calls)
{
uint32 i = funcctx->call_cntr;
+ char *values[NUM_FREESPACE_PAGES_ELEM];
+ int j;
+ /*
+ * Use a temporary values array, initially pointing to
fctx->values,
+ * so it can be reassigned w/o losing the storage for subsequent
+ * calls.
+ */
+ for (j = 0; j < NUM_FREESPACE_PAGES_ELEM; j++)
+ {
+ values[j] = fctx->values[j];
+ }
+
+
+ sprintf(values[0], "%u", fctx->record[i].reltablespace);
+ sprintf(values[1], "%u", fctx->record[i].reldatabase);
+ sprintf(values[2], "%u", fctx->record[i].relfilenode);
+ sprintf(values[3], "%u", fctx->record[i].relblocknumber);
+ /*
+ * Set (free) bytes to NULL for an index relation.
+ */
+ if (fctx->record[i].isindex == true)
+ {
+ values[4] = NULL;
+ }
+ else
+ {
+ sprintf(values[4], "%u", fctx->record[i].bytes);
+ }
/* Build and return the tuple. */
! tuple = BuildTupleFromCStrings(funcctx->attinmeta, values);
result = HeapTupleGetDatum(tuple);
Index: pg_freespacemap.sql.in
===================================================================
RCS file:
/projects/cvsroot/pgsql/contrib/pg_freespacemap/pg_freespacemap.sql.in,v
retrieving revision 1.2
diff -c -r1.2 pg_freespacemap.sql.in
*** pg_freespacemap.sql.in 27 Feb 2006 16:09:48 -0000 1.2
--- pg_freespacemap.sql.in 9 Mar 2006 03:42:15 -0000
***************
*** 11,17 ****
-- Create a view for convenient access.
CREATE VIEW pg_freespacemap AS
SELECT P.* FROM pg_freespacemap() AS P
! (blockid int4, relfilenode oid, reltablespace oid, reldatabase oid,
relblocknumber int8, blockfreebytes int4);
-- Don't want these to be available at public.
REVOKE ALL ON FUNCTION pg_freespacemap() FROM PUBLIC;
--- 11,17 ----
-- Create a view for convenient access.
CREATE VIEW pg_freespacemap AS
SELECT P.* FROM pg_freespacemap() AS P
! (reltablespace oid, reldatabase oid, relfilenode oid, relblocknumber
int8, bytes int4);
-- Don't want these to be available at public.
REVOKE ALL ON FUNCTION pg_freespacemap() FROM PUBLIC;
Index: README.pg_freespacemap
===================================================================
RCS file:
/projects/cvsroot/pgsql/contrib/pg_freespacemap/README.pg_freespacemap,v
retrieving revision 1.1
diff -c -r1.1 README.pg_freespacemap
*** README.pg_freespacemap 12 Feb 2006 03:55:53 -0000 1.1
--- README.pg_freespacemap 9 Mar 2006 03:43:16 -0000
***************
*** 34,45 ****
Column | references | Description
----------------+----------------------+------------------------------------
- blockid | | Id, 1.. max_fsm_pages
- relfilenode | pg_class.relfilenode | Refilenode of the relation.
reltablespace | pg_tablespace.oid | Tablespace oid of the relation.
reldatabase | pg_database.oid | Database for the relation.
relblocknumber | | Offset of the page in the relation.
! blockfreebytes | | Free bytes in the block/page.
There is one row for each page in the free space map.
--- 34,45 ----
Column | references | Description
----------------+----------------------+------------------------------------
reltablespace | pg_tablespace.oid | Tablespace oid of the relation.
reldatabase | pg_database.oid | Database for the relation.
+ relfilenode | pg_class.relfilenode | Refilenode of the relation.
relblocknumber | | Offset of the page in the relation.
! bytes | | Free bytes in the block/page, or
NULL
! | | for an index page (see below).
There is one row for each page in the free space map.
***************
*** 47,52 ****
--- 47,55 ----
Because the map is shared by all the databases, there are pages from
relations not belonging to the current database.
+ The free space map can contain pages for btree indexes if they were emptied
+ by a vacuum process. The bytes field is set to NULL in this case.
+
When the pg_freespacemap view is accessed, internal free space map locks are
taken, and a copy of the map data is made for the view to display.
This ensures that the view produces a consistent set of results, while not
***************
*** 58,91 ****
-------------
regression=# \d pg_freespacemap
! View "public.pg_freespacemap"
Column | Type | Modifiers
! ---------------+---------+-----------
! blockid | integer |
! relfilenode | oid |
reltablespace | oid |
reldatabase | oid |
relblocknumber | bigint |
! blockfreebytes | integer |
View definition:
! SELECT p.blockid, p.relfilenode, p.reltablespace, p.reldatabase,
p.relblocknumber, p.blockfreebytes
! FROM pg_freespacemap() p(blockid integer, relfilenode oid, reltablespace
oid, reldatabase oid, relblocknumber bigint, blockfreebytes integer);
! regression=# SELECT c.relname, m.relblocknumber, m.blockfreebytes
FROM pg_freespacemap m INNER JOIN pg_class c
ON c.relfilenode = m.relfilenode LIMIT 10;
! relname | relblocknumber | blockfreebytes
! ------------------------+----------------+----------------
! sql_features | 5 | 2696
! sql_implementation_info | 0 | 7104
! sql_languages | 0 | 8016
! sql_packages | 0 | 7376
! sql_sizing | 0 | 6032
! pg_authid | 0 | 7424
! pg_toast_2618 | 13 | 4588
! pg_toast_2618 | 12 | 1680
! pg_toast_2618 | 10 | 1436
! pg_toast_2618 | 7 | 1136
(10 rows)
regression=#
--- 61,93 ----
-------------
regression=# \d pg_freespacemap
! View "public.pg_freespacemap"
Column | Type | Modifiers
! ----------------+---------+-----------
reltablespace | oid |
reldatabase | oid |
+ relfilenode | oid |
relblocknumber | bigint |
! bytes | integer |
View definition:
! SELECT p.reltablespace, p.reldatabase, p.relfilenode, p.relblocknumber,
p.bytes
! FROM pg_freespacemap() p(reltablespace oid, reldatabase oid, relfilenode
oid, relblocknumber bigint, bytes integer);
! regression=# SELECT c.relname, m.relblocknumber, m.bytes
FROM pg_freespacemap m INNER JOIN pg_class c
ON c.relfilenode = m.relfilenode LIMIT 10;
! relname | relblocknumber | bytes
! ------------------------+----------------+--------
! sql_features | 5 | 2696
! sql_implementation_info | 0 | 7104
! sql_languages | 0 | 8016
! sql_packages | 0 | 7376
! sql_sizing | 0 | 6032
! pg_authid | 0 | 7424
! pg_toast_2618 | 13 | 4588
! pg_toast_2618 | 12 | 1680
! pg_toast_2618 | 10 | 1436
! pg_toast_2618 | 7 | 1136
(10 rows)
regression=#
---------------------------(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
