Paul is right that clustering does an exclusive lock on the table. Its my understanding though that a partially clustered table is better than a completely random ordered table. The reason being that when the scanner is picking up matches it does it by page and the more matches it can find on each page, the fewer pages it has to load up.
Also as I recall, I think each successive cluster is less taxing if you set your fill factors to lower than 100 as it will try to use the free space to move things in and since the table is partially clustered already, there is less work that needs to be done. (I could be very wrong on the last assumption though). There are also talks about revising the clustering strategy to be more in line with SQL Server (SQL Server maintains cluster on updates). Although I suppose we won't see this until probably 8.5 (possibly 8.4). Regina -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Paul Ramsey Sent: Thursday, July 31, 2008 5:13 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] query tuning Correct, clustering locks up the table while in process, so it's a pain for changing data, also the new data doesn't end up clustered, just tacked onto the end. BTW, your shared buffers of 100MB for a 16GB box is stingy. Give it 2-4GB, be generous. Not sure why you're having performance issues though... seems like simple stuff. P On Thu, Jul 31, 2008 at 1:59 PM, marc2112 <[EMAIL PROTECTED]> wrote: > 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 && >> '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 >> [email protected] >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> > > > _______________________________________________ > 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 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
