[postgis-users] query tuning

[marc2112]

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