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?
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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>
Show quoted text
=?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
=?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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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>
Show quoted text
=?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
=?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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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>
Show quoted text
=?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 donothing
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