[postgis-users] Tiger 2008 edges optimizations

[Alessandro Ferrucci]

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/1491daee/attachment.html>