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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
-----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 ctidOn 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 block position.
I'm fine with my regexp [ substring(ctid::text, '^.([^,]+)') ], but was just wondering if there were a more efficient way for it.
This could be a small nice to have e.g. in pageinspect.
regards,
Marc Mamin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general