Thread: Getting all entries in a single block with ctid

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

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

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

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

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

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