Getting all entries in a single block with ctid

On 16 Leden 2012, 15:07, Tore Halvorsen wrote:

Hi,

As I understand it, the ctid contains both the block number and an index
is
this block.
Is there a way to fetch all the table entries from the same block?

E.g. something like this:

select * from foo where ctid like '(123,%'

or ... ctid.block = 123

WHERE ctid >= '(123,0)'::tid AND ctid < '(124,0)'::tid

Tomas

On Mon, Jan 16, 2012 at 3:20 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
[...]

WHERE ctid >= '(123,0)'::tid AND ctid < '(124,0)'::tid

Ah, forgot a point here - without doing a sequential scan.

--
Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul]
<demo> 2012 Tore Halvorsen || +052 0553034554

On 16 Leden 2012, 15:28, Tore Halvorsen wrote:

On Mon, Jan 16, 2012 at 3:20 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
[...]

WHERE ctid >= '(123,0)'::tid AND ctid < '(124,0)'::tid

Ah, forgot a point here - without doing a sequential scan.

Hmmm, you could create an index on the ctid column, but that'd give you
bitmap index scan and not tid scan (which is probably what you're looking
for).

The only other solution is to check all possible items on the page. There
may be up to 291 items (although it depends on block size and
architecture, see MaxHeapTuplesPerPage in access/htup.h).

Something like this should work

ctid = '(123,0)'::tid OR ctid = '(123,1)'::tid OR ctid = '(123,2)'::tid
OR ... OR ctid = '(123,290)'::tid

But maybe someone will recommend a better solution.

Tomas

On Mon, Jan 16, 2012 at 4:08 PM, Tomas Vondra <tv@fuzzy.cz> wrote:

The only other solution is to check all possible items on the page. There
may be up to 291 items (although it depends on block size and
architecture, see MaxHeapTuplesPerPage in access/htup.h).

Nice to know.

Something like this should work

ctid = '(123,0)'::tid OR ctid = '(123,1)'::tid OR ctid = '(123,2)'::tid
OR ... OR ctid = '(123,290)'::tid

Yeah, that works, but it's kinda impractical... I'm mostly trying to figure
out how fragmented a table is,
by checking how must the blocks are shared - so that the worst one can be
scheduled for clustering...

But maybe someone will recommend a better solution.

Hopefully - thanks anyway :)

--
Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul]
<demo> 2012 Tore Halvorsen || +052 0553034554

On 16 Leden 2012, 17:15, Tore Halvorsen wrote:

On Mon, Jan 16, 2012 at 4:08 PM, Tomas Vondra <tv@fuzzy.cz> wrote:

The only other solution is to check all possible items on the page.
There
may be up to 291 items (although it depends on block size and
architecture, see MaxHeapTuplesPerPage in access/htup.h).

Nice to know.

Something like this should work

ctid = '(123,0)'::tid OR ctid = '(123,1)'::tid OR ctid = '(123,2)'::tid
OR ... OR ctid = '(123,290)'::tid

Yeah, that works, but it's kinda impractical... I'm mostly trying to
figure
out how fragmented a table is,
by checking how must the blocks are shared - so that the worst one can be
scheduled for clustering...

What about pgstattuple?

http://www.postgresql.org/docs/9.1/interactive/pgstattuple.html

Maybe it already does what you're trying to implement ...

Look at pageinspect module too

http://www.postgresql.org/docs/9.1/interactive/pageinspect.html

You're trying to do a quite low-level thing, so maybe this approach would
be more appropriate.

Tomas

On Mon, Jan 16, 2012 at 5:31 PM, Tomas Vondra <tv@fuzzy.cz> wrote:

What about pgstattuple?

http://www.postgresql.org/docs/9.1/interactive/pgstattuple.html

Maybe it already does what you're trying to implement ...

Look at pageinspect module too

http://www.postgresql.org/docs/9.1/interactive/pageinspect.html

You're trying to do a quite low-level thing, so maybe this approach would
be more appropriate.

That's true, but they are more concerned with the live/deleted status of
tuples and not the logical content... Perhaps generating statements is the
key for now...

--
Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul]
<demo> 2012 Tore Halvorsen || +052 0553034554

That's true, but they are more concerned with the live/deleted status of
tuples and not the logical content... Perhaps generating statements is the
key for now...

Creating a function that takes tid[] as a parameter works the way I want it
to :)

--
Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul]
<demo> 2012 Tore Halvorsen || +052 0553034554