[postgis-users] use index for "order by xmin(geom)"

[Mike Toews]

On 17 May 2012 09:10, Melchior Moos <melchior.moos at gmail.com> wrote:
> Sorry, actually st_xmin is the function I'm after. I want to select
> the whole table ordered by minimum x coordinate of the gemertries.
> When I do it the naive way postgresql needs half an hour to prepare
> the ordering before the first results are delivered, since my table is
> quite large. I thought that it could be somehow possible to skip this
> time since the ordering is already stored in the index on the geometry
> column...

One way to do this is to make an index using that function:

CREATE INDEX mytable_xmin_idx ON mytable (ST_Xmin(geom));

Then check the planner to make sure the index is being used:

EXPLAIN ANALYSE SELECT *
FROM mytable
ORDER BY ST_Xmin(geom);

See if that speeds things up.
-Mike