Alessandro,
I few other ideas/issues:
When you zoom out is the worse case because you have to look at all the
data. So anything you can do to organize the data to help this would
improve performance like:
Create separate tables for the different mtfcc codes that you want to
display and have them be inherited from a master table. This way when
you zoom out and only want the major roads you can just scan the major
roads tables and not all the other roads, but as you zoom in you can
switch to the master table that inherits all the roads.
you can also create separate geometry columns there one column is
simplified for use when you are zoomed out.
Build your gist indexes and cluster on them will help performance when
you are zoom in by requiring less disk seek because you data is
clustered spatially which will likely require few page loads.
-Steve W
http://imaptools.com/
Greg Williamson wrote:
Alessandro --
You might want to
a) post some details on your underlying file system (the part that
postgres uses, anyway). Postgres can only use one CPU per query so for
this sort of testing, disck I/O is probably more of a bottleneck than CPU.
b) and on changes you have made to the postgres config file [if none
then that right there is a hint] ... generally some tweaks need to be
made since the defaults settings are minimal and not suitable for any
real processing (work mem in particular, but also other knobs which
depend on your version of postgres ...
c) the output from postgis_full_version() and the details on postgres
(version/rev level) and the OS.
The estimate predicts about twice as many rows as there are, so I'll
ask: Have you run "ANALYZE" for the table since loading / changing /
deleting data ? A better estimate won't help this query directly but
might help in other areas. How many rows in that table, and how many
with "mtfcc" = 'S1100' ? If you have run a lot of updates or deletes
make sure to run a VACUUM as well (and check the logs to make sure that
there are no complaints); this will eliminate dead rows that the index
currently (might) be seeing.
That said, a few seconds for such a large data set might not be
unreasonable -- how big is that bounding box ? (I don't have access to
any real data currently so I can't really look for myself ...)
If it is getting a large enough area the planner may be deciding on
using the S1100 condition and then filtering results through the
geometry (that's how I read the output, but I'm rusty at this stuff).
Wading through lots of potential rows takes time for the backend, and
passing lots of rows takes yet more. If this BB is the whole US then the
planner is wisely ignoring your spatial index; try smaller scales and
you should see a GIST index being used.
As you point out, abstraction of this data would be mandatory for any
use that is zoomed out too any significant degree (even a single county
can have a huge number of street blocks). Creating other tables with
just highways, consolidated polygons with just the perimiter, etc. will
all enormously. When I had to deal with street data we made a table with
two or three of the highest level (A1, A2 at least) street codes only;
for parcel data we actually used a form of partioning, especially to
deal with requirements like "I want to see any street with "HECH" in its
name.
HTH,
Greg Williamson
*From:* Alessandro Ferrucci <[email protected]>
*To:* [email protected]
*Sent:* Friday, June 5, 2009 7:29:54 AM
*Subject:* [postgis-users] Tiger 2008 edges optimizations
Hello,
I've loaded the entire roads data set into 1 postGIS table. I've
created an SLD and am using geoserver as the middle man. Rendering the
map as you can imagine is quite slow at high levels. I've added a gist
index on the the_geom column and a btree index on the mtfcc column
(which is a string variable). I've already made postgres optimizations
on postgresql.conf and now I'm analyzing query times. I'm very much a
newbie in the postgres/postgis arena.
the box specs are:
dual core system with two intel 5160 @ 3.00 ghz
16 gb ram
censOS
I've turned on query logging on teh postgres instance and I took one of
the queries and ran EXPLAIN ANALYZE on it:
the query is:
SELECT "gid", "mtfcc",
encode(asBinary(force_2d("the_geom"),'XDR'),'base64') FROM
"public"."edges" WHERE ("the_geom" && GeometryFromText('POLYGON
((-177.51278882324738 -13.626259663701076, -177.51278882324738
103.63932637870316, 132.62382689416376 103.63932637870316,
132.62382689416376 -13.626259663701076, -177.51278882324738
-13.626259663701076))', 4269) AND "mtfcc" = 'S1100')
The analyze came back as:
"Bitmap Heap Scan on edges (cost=9602.70..992914.21 rows=725231
width=821) (actual time=926.943..3785.268 rows=369827 loops=1)"
" Recheck Cond: ((mtfcc)::text = 'S1100'::text)"
" Filter: (the_geom &&
'0103000020AD100000010000000500000042331BC4683066C0D7CC4C1BA5402BC042331BC4683066C0030030B9EAE859408E99D163F6936040030030B9EAE859408E99D163F6936040D7CC4C1BA5402BC042331BC4683066C0D7CC4C1BA5402BC0'::geometry)"
" -> Bitmap Index Scan on roads_idx (cost=0.00..9421.39 rows=725231
width=0) (actual time=840.086..840.086 rows=369827 loops=1)"
" Index Cond: ((mtfcc)::text = 'S1100'::text)"
"Total runtime: 3848.870 ms"
There are a lot of these queries that are run when I'm rendering the
roads from a high zoom level (encompasses the entire U.S.). I will make
optimization changes (like line reduction) as well but for now I want to
make sure that my indeces are being used properly. The "Bitmap Heap
Scan" looks like it's doing a table scan on 725231 rows ...
any assistance?
thanks
--
Signed,
Alessandro Ferrucci
------------------------------------------------------------------------
_______________________________________________
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
