Postgres Pro
Downloads
Home   >   mailing lists

Thread: GIST index : order Hack : getting the order used by CLUSTER .. USING my_index

GIST index : order Hack : getting the order used by CLUSTER .. USING my_index

From
Rémi Cura
Date:
Hey List,

I would like to be able to get the rows following the order of an index (*NOT* getting an order by accelerated, but only an order defined by an index).

Something like this :

SELECT my_row
FROM my_table
ORDER BY the_index ASC

where the_index is a GIST index over points.

I know there is a possibility as it is exactly what the command
CLUSTER my_table USING the_index 
does.


I read the following page : http://www.postgresql.org/docs/9.3/static/indexes-ordering.html, but it is not of great help.
How much of a hack is it?

Cheers,

Rémi-C

Re: GIST index : order Hack : getting the order used by CLUSTER .. USING my_index

From
Tom Lane
Date:
=?UTF-8?Q?R=C3=A9mi_Cura?= <remi.cura@gmail.com> writes:
> I would like to be able to get the rows following the order of an index
> (*NOT* getting an order by accelerated, but only an order defined by an
> index).

Since a GiST index hasn't got any specific internal order, I fail to see
the point of this.

            regards, tom lane

Re: GIST index : order Hack : getting the order used by CLUSTER .. USING my_index

From
Rémi Cura
Date:
Hello,

I'm interested in the tree structure inherent to the gist indexing.
I was thinking to retrieve it from order of index.
Do you know how I could access it directly?

My use case would be to take advantage of this gist ordering to order 2D points
s1 : N1 N2 .. Nn 
so that for any given t<n,  s2 : [N1 Nt] points are an extract of s1 which is well spread (spatially speaking).

Ideally I would prefer to use the quadtree index in contrib rather than the Gist R Tree.

Cheers,

Rémi-C




2013/10/24 Tom Lane <tgl@sss.pgh.pa.us>
Rémi Cura <remi.cura@gmail.com> writes:
> I would like to be able to get the rows following the order of an index
> (*NOT* getting an order by accelerated, but only an order defined by an
> index).

Since a GiST index hasn't got any specific internal order, I fail to see
the point of this.

                        regards, tom lane

Re: GIST index : order Hack : getting the order used by CLUSTER .. USING my_index

From
Tom Lane
Date:
=?UTF-8?Q?R=C3=A9mi_Cura?= <remi.cura@gmail.com> writes:
> I'm interested in the tree structure inherent to the gist indexing.
> I was thinking to retrieve it from order of index.

How?  A SQL query would have no idea where the index page boundaries were
in the sequence of retrieved tuples.

> Do you know how I could access it directly?

I don't think there's any way to do that without modifying the GiST code.
What you really care about here is the contents of the upper index levels,
which is something that's not exposed at all outside the index AM.

            regards, tom lane

Re: GIST index : order Hack : getting the order used by CLUSTER .. USING my_index

From
Rémi Cura
Date:
Ok,
thank you Tom for this precise answer !

I don't understand how the CLUSTER .. USING index command work then.
It is supposed to rewrite on disk following index order. Does it do nothing for GIST index?

Cheers,
Rémi-C


2013/10/24 Tom Lane <tgl@sss.pgh.pa.us>
Rémi Cura <remi.cura@gmail.com> writes:
> I'm interested in the tree structure inherent to the gist indexing.
> I was thinking to retrieve it from order of index.

How?  A SQL query would have no idea where the index page boundaries were
in the sequence of retrieved tuples.

> Do you know how I could access it directly?

I don't think there's any way to do that without modifying the GiST code.
What you really care about here is the contents of the upper index levels,
which is something that's not exposed at all outside the index AM.

                        regards, tom lane

Re: GIST index : order Hack : getting the order used by CLUSTER .. USING my_index

From
Tom Lane
Date:
=?UTF-8?Q?R=C3=A9mi_Cura?= <remi.cura@gmail.com> writes:
> I don't understand how the CLUSTER .. USING index command work then.
> It is supposed to rewrite on disk following index order. Does it do nothing
> for GIST index?

Nobody has ever demonstrated that CLUSTER has any value for anything
except btree indexes.  It seems likely to me that it'd actually be
counterproductive for indexes like GiST, which depend on data arriving in
random order for the highest index levels to end up well-distributed.

            regards, tom lane

Re: GIST index : order Hack : getting the order used by CLUSTER .. USING my_index

From
Rémi Cura
Date:
Great,
thanks.

Now you say that I never saw any improvement when clustering table with gist.
You just saved me a lot of unnecessary queries :-)

Cheers,

Rémi-C


2013/10/24 Tom Lane <tgl@sss.pgh.pa.us>
Rémi Cura <remi.cura@gmail.com> writes:
> I don't understand how the CLUSTER .. USING index command work then.
> It is supposed to rewrite on disk following index order. Does it do nothing
> for GIST index?

Nobody has ever demonstrated that CLUSTER has any value for anything
except btree indexes.  It seems likely to me that it'd actually be
counterproductive for indexes like GiST, which depend on data arriving in
random order for the highest index levels to end up well-distributed.

                        regards, tom lane