Обсуждение: how to extract the page "address" from the ctid

Hello,

while waiting for the new BRIN and improved GIN Indexes :-)

I'd like to get a rough feeling, how some column's values are distributed on the internal pages.

e.g.:

SELECT c, count(*) FROM
(
       SELECT substring(ctid::text, '^.([^,]+)'),
        count(distinct myColumn) as c
        FROM myTable
        GROUP BY substring(ctid::text, '^.([^,]+)')
)foo
GROUP BY c order by 1 desc;


Is there a quicker way to extract the page reference from the ctid?

regards,

Marc Mamin

On Tue, Jun 30, 2015 at 10:56 PM, Marc Mamin <M.Mamin@intershop.de> wrote:
> while waiting for the new BRIN and improved GIN Indexes :-)
>
> I'd like to get a rough feeling, how some column's values are distributed on the internal pages.
> Is there a quicker way to extract the page reference from the ctid?

Isn't it something that pageinspect can do directly for you? It has
been extended for brin indexes.
http://www.postgresql.org/docs/devel/static/pageinspect.html
--
Michael

> -----Original Message-----
> From: Michael Paquier [mailto:michael.paquier@gmail.com]
> Sent: Mittwoch, 1. Juli 2015 07:05
> To: Marc Mamin
> Cc: Postgres General
> Subject: Re: [GENERAL] how to extract the page "address" from the ctid
> 
> On Tue, Jun 30, 2015 at 10:56 PM, Marc Mamin <M.Mamin@intershop.de>
> wrote:
> > while waiting for the new BRIN and improved GIN Indexes :-)
> >
> > I'd like to get a rough feeling, how some column's values are
> distributed on the internal pages.
> > Is there a quicker way to extract the page reference from the ctid?
> 
> Isn't it something that pageinspect can do directly for you? It has
> been extended for brin indexes.
> http://www.postgresql.org/docs/devel/static/pageinspect.html
> --
> Michael

As I understand it, pageinspect allows to get insight in given pages, 
but not to compute overall page distribution analysis of given values.

There is  a function tidsend(tid) that seems to return a row position within a block, but no equivalent to return the
blockposition.
 
I'm fine with my regexp [ substring(ctid::text, '^.([^,]+)') ], but was just wondering if there were a more efficient
wayfor it.
 
This could be a small nice to have e.g. in pageinspect.

regards, 
Marc Mamin