[postgis-users] pg7.4.1 performance problem

Daryl Herzmann akrherz at iastate.edu
Tue Feb 17 22:29:26 PST 2004


Hiya PostGISers,

I have a performance problem on a recently migrated 7.3.2 database to 
7.4.1.  Fully patched RH 8.  PostGIS 0.8.1, GEOS 1

Here is my first query that looks good:

explain analyze 
  SELECT asbinary(force_collection(force_2d(geo)),'NDR'),oid::text from  
     (select 
         d.oid as oid, d.rainfall / 25.4 as rainfall, 
         h.the_geom as geo from 
         daily_rainfall_2003 d , hrap_polygons h 
      WHERE d.hrap_i = h.hrap_i and d.valid = '2003-11-25') as foo ;
                                                                            
QUERY PLAN                                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=2082.92..3259.71 rows=2627 width=252) (actual 
time=13.800..13.809 rows=1 loops=1)
   Merge Cond: ("outer".hrap_i = "inner".hrap_i)
   ->  Sort  (cost=2082.92..2101.70 rows=7510 width=6) (actual 
time=0.302..0.332 rows=66 loops=1)
         Sort Key: d.hrap_i
         ->  Index Scan using daily_rainfall_valid_idx on 
daily_rainfall_2003 d  (cost=0.00..1599.48 rows=7510 width=6) (actual 
time=0.047..0.210 rows=66 loops=1)
               Index Cond: ("valid" = '2003-11-25'::date)
   ->  Index Scan using hrap_polygons_hrap_i_idx on hrap_polygons h  
(cost=0.00..1073.16 rows=7988 width=252) (actual time=0.013..8.753 
rows=5102 loops=1)
 Total runtime: 13.931 ms
(8 rows)

-----------------------------
Now we add in the bounding box constraint that mapserver generates and we 
get
------------------------------

explain analyze
  SELECT asbinary(force_collection(force_2d(geo)),'NDR'),oid::text from  
     (select 
         d.oid as oid, d.rainfall / 25.4 as rainfall, 
         h.the_geom as geo from 
         daily_rainfall_2003 d , hrap_polygons h 
      WHERE d.hrap_i = h.hrap_i and d.valid = '2003-11-25') as foo 
   WHERE geo && 
setSRID('BOX3D(159999.999983024 4424687.49997101,
               760000.000004331 4875312.5)'::BOX3D, 26915 );
                                                                           
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------- 
Nested Loop  (cost=0.00..2786.49 rows=1 width=252) (actual 
time=1109.977..1778.253 rows=1 loops=1)
   Join Filter: ("inner".hrap_i = "outer".hrap_i)
   ->  Index Scan using hrap_polygons_hrap_i_idx on hrap_polygons h  
(cost=0.00..1093.13 rows=1 width=252) (actual time=0.053..23.555 rows=7988 
loops=1)
         Filter: (the_geom && 'SRID=26915;BOX3D(159999.999983024 
4424687.49997101 0,760000.000004331 4875312.5 0)'::geometry)
   ->  Index Scan using daily_rainfall_valid_idx on daily_rainfall_2003 d  
(cost=0.00..1599.48 rows=7510 width=6) (actual time=0.009..0.158 rows=66 
loops=7988)
         Index Cond: ("valid" = '2003-11-25'::date)
 Total runtime: 1778.341 ms


Whoaaa, this isn't good.  This is just a modest example.  Queries on dates 
with more data basically don't return (impatience on my part).  Well, 
before I ask the pgsql-performance folks, any thoughts from the PostGIS 
side?  

Thanks,
  Daryl

-- 
/**
 * Daryl Herzmann (akrherz at iastate.edu)
 * Program Assistant -- Iowa Environmental Mesonet
 * http://mesonet.agron.iastate.edu
 */




More information about the postgis-users mailing list