It would probably help if you clustered on the gist index. Do ALTER TABLE users CLUSTER ON users_locbbox; CLUSTER users; Also what kind of processors and on board ram do you have? Hope that helps, Regina
_____ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of marc2112 Sent: Thursday, July 31, 2008 3:31 PM To: postgis-users@postgis.refractions.net Subject: [postgis-users] query tuning Hi Folks, I've got a slow running query. When I break it down to just the spatial constraint on one table, I find that the query takes 8s. I need my broader query to take < 1s so have a long way to go on just the spatial part. Hopefully you guys will have some ideas for me... Here's what I've got: Query: SELECT u.username,u.locbbox, y(u.locpoint) AS latitude, x(u.locpoint) AS longitude FROM users u WHERE u.locbbox && SetSrid( 'BOX(-73.795166015625 9.42738628387451,-69.49951171875 11.813588142395)'::box2d, 4326 ) Explain Analyze: "Bitmap Heap Scan on users u (cost=741.34..43666.77 rows=15789 width=486) (actual time=29.730..7321.358 rows=10687 loops=1)" " Filter: (locbbox && '0103000020E6100000010000000500000000000000E47252C000000060D2DA224000000000E 47252C0000000A08EA0274000000000F85F51C0000000A08EA0274000000000F85F51C000000 060D2DA224000000000E47252C000000060D2DA2240'::geometry)" " -> Bitmap Index Scan on users_locbbox (cost=0.00..737.39 rows=15789 width=0) (actual time=26.981..26.981 rows=10703 loops=1)" " Index Cond: (locbbox && '0103000020E6100000010000000500000000000000E47252C000000060D2DA224000000000E 47252C0000000A08EA0274000000000F85F51C0000000A08EA0274000000000F85F51C000000 060D2DA224000000000E47252C000000060D2DA2240'::geometry)" "Total runtime: 7328.359 ms" Index: CREATE INDEX users_locbbox ON users USING gist (locbbox); Some Stats (let me know what else would be helpful): Rows in users table: 1.85mm Table Size: ~4GB users_locbbox index size: 190MB Tuning done: shared_buffers = 100MB work_mem = 100MB max_fsm_pages = 153600 random_page_cost = 3.0 cpu_tuple_cost = 0.1 effective_cache_size = 8GB default_statistics_target = 100 Tables in the query are vacuum/analyzed daily.
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
