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. P. On Thu, Mar 12, 2009 at 11:13 AM, Dylan Keon <[email protected]> wrote: > On Thu, Mar 5, 2009 at 11:32 PM, Raphaël Jacquot <[email protected]> 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 > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
