[postgis-users] Slow query performance on large table

Dylan Keon dbkeon.ml at gmail.com
Thu Mar 12 11:13:47 PDT 2009


On Thu, Mar 5, 2009 at 11:32 PM, Raphaël Jacquot <sxpert at sxpert.org> wrote:

> one difference I can see is that x is a smallint in one case, and an integer in the other
>
> apart from that, you should probably create an index on (x, y), it should make things much
> faster.
> see multicolumn index here
> http://www.postgresql.org/docs/8.3/interactive/indexes-multicolumn.html

We had to define x as an integer on that table due to the larger grid
size.  But we figured int vs. smallint shouldn't really affect query
performance in this case.

The multicolumn index worked perfectly - we are now getting very fast
queries across x and y.  Thanks for the tip!

Dylan



More information about the postgis-users mailing list