[postgis-users] query tuning

[marc2112]

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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080731/8f24e3c8/attachment.html>