[postgis-users] query tuning

[Paul Ramsey]

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 <postgis at mbreslow.net> 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 <lr at pcorp.us> 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: postgis-users-bounces at postgis.refractions.net
>> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of marc2112
>> Sent: Thursday, July 31, 2008 3:31 PM
>> To: postgis-users at 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 &&
>> '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
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>