Обсуждение: GIST index : order Hack : getting the order used by CLUSTER .. USING my_index
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?
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
=?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
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:Since a GiST index hasn't got any specific internal order, I fail to see
> 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).
the point of this.
regards, tom lane
=?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
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:How? A SQL query would have no idea where the index page boundaries were
> I'm interested in the tree structure inherent to the gist indexing.
> I was thinking to retrieve it from order of index.
in the sequence of retrieved tuples.I don't think there's any way to do that without modifying the GiST code.
> Do you know how I could access it directly?
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
=?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
Great,
thanks.
Now you say that I never saw any improvement when clustering table with gist.
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:Nobody has ever demonstrated that CLUSTER has any value for anything
> 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?
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