It's a pretty diesel box - 2 quad core xeons, 16 GB RAM, dedicated disks for data and txnlog.
It's my understanding that a clustered index only helps when the data isn't really changing. I read that it clusters (basically sorts the data per the index) when you create the index but subsequent inserts/updates still get written on new pages. Am I off there? On Thu, Jul 31, 2008 at 4:46 PM, Paragon Corporation <[EMAIL PROTECTED]> wrote: > 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:* [email protected] > *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 && > '0103000020E6100000010000000500000000000000E47252C000000060D2DA224000000000E47252C0000000A08EA0274000000000F85F51C0000000A08EA0274000000000F85F51C000000060D2DA224000000000E47252C000000060D2DA2240'::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 && > '0103000020E6100000010000000500000000000000E47252C000000060D2DA224000000000E47252C0000000A08EA0274000000000F85F51C0000000A08EA0274000000000F85F51C000000060D2DA224000000000E47252C000000060D2DA2240'::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 > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > >
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
