[postgis-users] Query performance help

[Obe, Regina DND\MIS]

Is your sumlev stored as an integer or as text and does it need to be stored
as text.  It looks like there is possibly a needless conversion to text
taking place here which makes me think you have the field as a varchar or
text datatype when you may not need to.  I think integer indexes are more
efficient than text/varchar indexes (at least they are in other dbs I've
worked with).

-----Original Message-----
From: Stephen Woodbridge [mailto:woodbri at swoodbridge.com] 
Sent: Wednesday, March 23, 2005 9:55 AM
To: PostGIS Users Discussion
Subject: [postgis-users] Query performance help


Hi all,

This is slightly OT, but I just migrated from MySQL to postgres/postgis 
and I'm having a problem with query performance. Any thoughts would be 
appreciated as I feel this is just something dumb because don't know the 
ins and outs of postgres yet.

I have three tables abbreviated but like (if you are familiar with it, 
these are from the census summary file info) and my goal is to generate 
thematic maps. In this case of zipcode tabulation areas (zcta):

table: sf3geo
   zcta5
   sumlev
   logrecno

table: dp3
   logrecno
   lots of attribute cols

table: zcta
   zcta5
   the_geom

So I need to select all the sf3geo.sumlev=871 to just get the zcta 
records and join it to dp3 based on logrecno and join it to zcta based 
on zcta5.

Just trying to do the first join is a killer!

explain select b.zcta5, round(a.dp30063/200001*8) as income from sf3geo 
b, dp3 a where b.sumlev=871 and b.logrecno=a.logrecno;

Nested Loop  (cost=0.00..460665.14 rows=132237 width=8)
   ->  Index Scan using sf3geo_sumlev_idx on sf3geo b 
(cost=0.00..29652.79 rows=7491 width=15)
         Index Cond: ((sumlev)::text = '871'::text)
   ->  Index Scan using dp3_logrecno_idx on dp3 a  (cost=0.00..57.14 
rows=18 width=15)
         Index Cond: (("outer".logrecno)::text = (a.logrecno)::text)

In fact, just counting the rows is slow:

explain select count(*) from sf3geo where sumlev=871;

Aggregate  (cost=29671.52..29671.52 rows=1 width=0)
   ->  Index Scan using sf3geo_sumlev_idx on sf3geo 
(cost=0.00..29652.79 rows=7491 width=0)
         Index Cond: ((sumlev)::text = '871'::text)

So I am wondering what am I missing that make this go faster? Can using 
view improve performance? For example by pre-filtering the sf3geo to 
only show the sumlev=871 records?

I did vaccuum analyze all the tables, but I changed some indexes so may 
be I need to do that again.

-Steve

_______________________________________________
postgis-users mailing list postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users