[postgis-users] Slow query performance on large table

[Paul Ramsey]

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 <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
>> 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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>