Thread: Why are there no inequality scans for ctid?

Hi,
while working on removing bloat from some table, I had to use ltos of
logic simply because there are no (idnexable) inequality scans for
ctids.

Is it because just noone thought about adding them, or are there some
more fundamental issues?

I could imagine that things like:

select * from table where ctid @ '123' could return all rows from 123rd
page, or I could:
select * from table where ctid >= '(123,0)' and ctid < '(124,0)';

Having such operators work would greatly improve bloat reducing
options.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

On Fri, Jun 28, 2013 at 2:07 PM, hubert depesz lubaczewski
<depesz@depesz.com> wrote:
> Hi,
> while working on removing bloat from some table, I had to use ltos of
> logic simply because there are no (idnexable) inequality scans for
> ctids.
>
> Is it because just noone thought about adding them, or are there some
> more fundamental issues?
>
> I could imagine that things like:
>
> select * from table where ctid @ '123' could return all rows from 123rd
> page, or I could:
> select * from table where ctid >= '(123,0)' and ctid < '(124,0)';
>
> Having such operators work would greatly improve bloat reducing
> options.

How would this be helpful for general use cases? Querying on tids on a
specific page doesn't seem too useful for any other case than the one
you mentioned above, and IMHO it seems to be the job of vacuum.

I may be missing something here though.

Regards,
Atri



--
Regards,

Atri
l'apprenant

On Fri, Jun 28, 2013 at 02:21:10PM +0530, Atri Sharma wrote:
> How would this be helpful for general use cases? Querying on tids on a
> specific page doesn't seem too useful for any other case than the one
> you mentioned above, and IMHO it seems to be the job of vacuum.
> I may be missing something here though.

Vacuum doesn't move rows around (as far as I can tell by running vacuum
~ 100 times on bloated table).

And as for general case - sure. It's not really useful aside from bloat
removal, but I think that bloat removal is important enough to warrant
some help from Pg.

Best regards,

depesz

Re: hubert depesz lubaczewski 2013-06-28 <20130628085246.GA25949@depesz.com>
> On Fri, Jun 28, 2013 at 02:21:10PM +0530, Atri Sharma wrote:
> > How would this be helpful for general use cases? Querying on tids on a
> > specific page doesn't seem too useful for any other case than the one
> > you mentioned above, and IMHO it seems to be the job of vacuum.
> > I may be missing something here though.
>
> Vacuum doesn't move rows around (as far as I can tell by running vacuum
> ~ 100 times on bloated table).
>
> And as for general case - sure. It's not really useful aside from bloat
> removal, but I think that bloat removal is important enough to warrant
> some help from Pg.

It would also be useful for querying broken tables where you "SELECT *
FROM badtable WHERE ctid < '(123,0)';" to avoid dying on a bad block.

Christoph
--
cb@df7cb.de | http://www.df7cb.de/