[postgis-users] Tiger 2008 edges optimizations

[Greg Williamson]

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 <alessandroferrucci at gmail.com>
To: postgis-users at postgis.refractions.net
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



      
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090605/7fecafdf/attachment.html>