[postgis-users] Slow query performance on large table
pramsey at opengeo.org
Thu Mar 12 11:28:00 PDT 2009
Once you see the answer, the explanation becomes clearer...
WHERE x=4 AND y=5
with independent x and y indexes means quite a large number of page
access for both indexes, and then an index merge, but with a multi-key
index, many fewer accesses, and no index merge.
On Thu, Mar 12, 2009 at 11:13 AM, Dylan Keon <dbkeon.ml at gmail.com> wrote:
> 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
>> see multicolumn index here
> 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!
> postgis-users mailing list
> postgis-users at postgis.refractions.net
More information about the postgis-users