SEO

Hi,

I tried pg_freespacemap and found strange result:

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)

Is it possible that a free space map entry has 0 blockfreebytes?
--
Tatsuo Ishii
SRA OSS, Inc. Japan

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

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 Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

> 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

Tatsuo Ishii <ishii@sraoss.co.jp> 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

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

Tom Lane wrote:
> Tatsuo Ishii <ishii@sraoss.co.jp> 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 majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

> > 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

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 Herrera http://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

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