3

I'm trying to efficiently serve forecast streamflow WMS images from a PostGIS database using Geoserver 2.9x. My dataset (currently > 5TB) consists of static polylines (2.3 million of them) that each have a number of values that vary over the time dimension, as well as a forecast time dimension and a model run dimension.

To manage this dataset, I've set up a static table containing the geometries of each polyline, with an id assigned to each polyline. Additionally I've set up a 'times' table which assigns a unique id to each time/forecast time/model run combination. Thirdly, I've put the data in a huge partitioned table that includes the index of each polyline (into the static table) as well as an index into the times table. The data table is partitioned by the time id, so each partition contains only values applicable at a particular time/forecast time/model combination.

So I have:

static table: (2.3 million rows)

                                                       Table     "public.staticflow"
   Column    |              Type              |                          Modifiers                          | Storage | Stats target | Description 
--------------+--------------------------------+-------------------------------------------------------------+---------+--------------+-------------
id           | integer                        | not null default nextval('staticflow_id_seq'::regclass) | plain   |              | 
shape_length | double precision               |                                                             | plain   |              | 
strorder     | integer                        |                                                             | plain   |              | 
wkb_geometry | geometry(MultiLineString,4269) |                                                             | main    |              | 
Indexes:
   "staticflow_pkey" PRIMARY KEY, btree (id)
   "staticflow_id_idx" btree (id)
   "staticflow_wkb_geometry_geom_idx" gist (wkb_geometry) CLUSTER

times table: (10,000 rows)

                                                     Table "public.flow_times"
  Column   |            Type             |                           Modifiers                           | Storage  | Stats target | Description 
-----------+-----------------------------+---------------------------------------------------------------+----------+--------------+-------------
id        | integer                     | not null default nextval('flow_times_id_seq'::regclass)      | plain    |              | 
validtime | timestamp without time zone |                                                               | plain    |              | 
inittime  | timestamp without time zone |                                                               | plain    |              | 
model     | character varying(30)       |                                                               | extended |              | 
Indexes:
    "flow_times_pkey" PRIMARY KEY, btree (id)
    "flow_times_id_idx" btree (id)
    "flow_times_inittime_idx" btree (inittime)
    "flow_times_model_idx" btree (model)
    "flow_times_validtime_idx" btree (validtime)

data table: (10,000 partitions, each with 2.3 million rows)

Table "public.flow"
   Column   |  Type   | Modifiers | Storage | Stats target | Description 
------------+---------+-----------+---------+--------------+-------------
 station_id | integer |           | plain   |              | 
 timeid     | integer |           | plain   |              | 
 streamflow | real    |           | plain   |              | 
 velocity   | real    |           | plain   |              | 
Child tables: flow_15192,
              flow_15193,
              flow_15194,
             ...

each partition has the index/constraints, e.g. for flow_15192: Indexes: "flow_15192_station_id_idx" btree (station_id) Check constraints: "flow_15192_timeid_check" CHECK (timeid = 15192) Inherits: flow

To serve this with GeoServer, I created a postgres view that puts this all together, and use a CQL filter with the time id (obtained through a separate query) to query the WMS against this view.

create flow_view SELECT staticflow.id,
    flow_times.id AS timeid,
    flow_times.validtime,
    flow_times.inittime,
    flow_times.model,
    staticflow.strorder,
    flow.streamflow,
    flow.velocity,
    staticflow.shape_length,
    staticflow.wkb_geometry
   FROM staticflow,
    flow_times,
    flow
  WHERE flow.station_id = staticflow.id AND flow_times.id = flow.timeid;

I've looked at the query plan for the queries that GeoServer is sending to Postgres, e.g:

explain analyze SELECT "streamflow",encode(ST_AsBinary(ST_Simplify(ST_Force2D("wkb_geometry"), 1.0357088025898521E-4)),'base64') as "wkb_geometry" FROM "public"."flow_view" WHERE  ("timeid" = 81388 AND "timeid" IS NOT NULL  AND "wkb_geometry" && ST_GeomFromText('POLYGON ((-115.13723373413087 41.01267720039345, -115.13723373413087 41.04660520510985, -115.09225845336915 41.04660520510985, -115.09225845336915 41.01267720039345, -115.13723373413087 41.01267720039345))', 4269) AND (("streamflow" > -1.0E-4 AND "streamflow" IS NOT NULL  AND "streamflow" < 5.0) OR ("streamflow" > 4.9999 AND "streamflow" IS NOT NULL  AND "streamflow" < 10.0) OR ("streamflow" > 9.9999 AND "streamflow" IS NOT NULL  AND "streamflow" < 15.0) OR ("streamflow" > 14.9999 AND "streamflow" IS NOT NULL  AND "streamflow" < 20.0) OR ("streamflow" > 19.9999 AND "streamflow" IS NOT NULL  AND "streamflow" < 25.0) OR ("streamflow" > 24.9999 AND "streamflow" IS NOT NULL  AND "streamflow" < 30.0) OR ("streamflow" > 29.9999 AND "streamflow" IS NOT NULL  AND "streamflow" < 100000.0)));


     QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.70..163.24 rows=11 width=465) (actual time=0.101..0.200 rows=9 loops=1)
   ->  Index Only Scan using conus_flow_times_id_idx on conus_flow_times  (cost=0.29..8.31 rows=1 width=4) (actual time=0.020..0.020 rows=1 loops=1)
         Index Cond: ((id IS NOT NULL) AND (id = 81388))
         Heap Fetches: 1
   ->  Nested Loop  (cost=0.41..154.71 rows=11 width=469) (actual time=0.045..0.104 rows=9 loops=1)
         ->  Index Scan using staticflow_wkb_geometry_geom_idx on staticflow  (cost=0.41..52.62 rows=12 width=465) (actual time=0.026..0.058 rows=9 loops=1)
               Index Cond: (wkb_geometry && '0103000020AD100000010000000500000001000070C8C85CC01F8C10689F81444001000070C8C85CC095E2CB28F785444001000090E7C55CC095E2CB28F785444001000090E7C55CC01F8C10689F81444001000070C8C85CC01F8C10689F814440'::geometry)
         ->  Append  (cost=0.00..8.49 rows=2 width=12) (actual time=0.004..0.005 rows=1 loops=9)
               ->  Seq Scan on conus_flow  (cost=0.00..0.00 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=9)
                     Filter: ((streamflow IS NOT NULL) AND (timeid = 81388) AND (staticflow.id = station_id) AND (((streamflow > (-0.0001)::double precision) AND (streamflow < 5::double precision)) OR ((streamflow > 4.9999::double precision) AND (streamflow < 10::double precision)) OR ((streamflow > 9.9999::double precision) AND (streamflow < 15::double precision)) OR ((streamflow > 14.9999::double precision) AND (streamflow < 20::double precision)) OR ((streamflow > 19.9999::double precision) AND (streamflow < 25::double precision)) OR ((streamflow > 24.9999::double precision) AND (streamflow < 30::double precision)) OR ((streamflow > 29.9999::double precision) AND (streamflow < 100000::double precision))))
               ->  Index Scan using conus_flow_81388_station_id_idx on conus_flow_81388  (cost=0.43..8.49 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=9)
                     Index Cond: (station_id = staticflow.id)
                     Filter: ((streamflow IS NOT NULL) AND (timeid = 81388) AND (((streamflow > (-0.0001)::double precision) AND (streamflow < 5::double precision)) OR ((streamflow > 4.9999::double precision) AND (streamflow < 10::double precision)) OR ((streamflow > 9.9999::double precision) AND (streamflow < 15::double precision)) OR ((streamflow > 14.9999::double precision) AND (streamflow < 20::double precision)) OR ((streamflow > 19.9999::double precision) AND (streamflow < 25::double precision)) OR ((streamflow > 24.9999::double precision) AND (streamflow < 30::double precision)) OR ((streamflow > 29.9999::double precision) AND (streamflow < 100000::double precision))))
 Planning time: 3041.998 ms
 Execution time: 1.192 ms

(the streamflow filter is for styling the polylines based on streamflow value (using an SLD). Removing this does not seem to speed up the queries significantly.)

My questions are

  1. It seems that for every session, there is a one-time penalty for the first query (several minutes) after which queries tend to run much quicker (about 10 seconds for all the tiles to be served). What is going on here?
  2. Is there a way to optimize GeoServer's queries against this schema, or a more efficient query to try?
  3. other postgres optimizations that might help?

I'm pretty new to both GeoServer and PostGIS and have a sinking feeling that I could be structuring this dataset and queries more efficiently, but I've run out of ideas and don't have any postgres experts at work to ask, so I'm posting here.

closed as too broad by PolyGeo Mar 4 '17 at 0:34

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Welcome to GIS SE! As a new user be sure to take the Tour to learn about our focussed Q&A format. For PostgreSQL experts the place to research/ask is the Database Administrators Stack Exchange. PostGIS and GeoServer are on-topic here. However, it sounds like you have working SQL code that you wish to optimize so maybe the Code Review Stack Exchange is worth you considering. – PolyGeo Mar 4 '17 at 0:38
  • It's a pretty interesting question, though I agree with PolyGeo that it is quite broad and I'm not sure where you are most likely to get an asnswer. – John Powell Mar 5 '17 at 19:08
  • 1
    These kind of questions in my experience do not have a simple straight answer, but can be addressed on mailing list though conversation (instead of a Q&A format). I'd suggest to ask the same on the postgis users list, the issue seems mostly data access related. – Andrea Aime Mar 6 '17 at 9:08