<div dir="ltr"><p>Hi,<br></p><p>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.</p>
<p>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.</p>
<p>So I have:</p>
<p>static table: (2.3 million rows)</p>
<pre class="gmail-m_8125139665059215853gmail-default gmail-m_8125139665059215853gmail-prettyprint gmail-m_8125139665059215853gmail-prettyprinted"><code><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Table</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-str">"public.staticflow"</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-typ">Column</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Type</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Modifiers</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Storage</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Stats</span><span class="gmail-m_8125139665059215853gmail-pln"> target </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Description</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-pun">--------------+---------------<wbr>-----------------+------------<wbr>------------------------------<wbr>-------------------+---------+<wbr>--------------+-------------</span><span class="gmail-m_8125139665059215853gmail-pln">
id </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> integer </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-kwd">not</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-kwd">null</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-kwd">default</span><span class="gmail-m_8125139665059215853gmail-pln"> nextval</span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-str">'staticflow_id_seq'</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-pln">r<wbr>egclass</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> plain </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln">
shape_length </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> plain </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln">
strorder </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> integer </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> plain </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln">
wkb_geometry </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> geometry</span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-typ">MultiLineString</span><span class="gmail-m_8125139665059215853gmail-pun">,</span><span class="gmail-m_8125139665059215853gmail-lit">4269</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> main </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-typ">Indexes</span><span class="gmail-m_8125139665059215853gmail-pun">:</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-str">"staticflow_pkey"</span><span class="gmail-m_8125139665059215853gmail-pln"> PRIMARY KEY</span><span class="gmail-m_8125139665059215853gmail-pun">,</span><span class="gmail-m_8125139665059215853gmail-pln"> btree </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">id</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-str">"staticflow_id_idx"</span><span class="gmail-m_8125139665059215853gmail-pln"> btree </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">id</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-str">"staticflow_wkb_geometry_geom_<wbr>idx"</span><span class="gmail-m_8125139665059215853gmail-pln"> gist </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">wkb_geometry</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> CLUSTER</span></code></pre>
<p>times table: (10,000 rows)</p>
<pre class="gmail-m_8125139665059215853gmail-default gmail-m_8125139665059215853gmail-prettyprint gmail-m_8125139665059215853gmail-prettyprinted"><code><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Table</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-str">"public.flow_times"</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-typ">Column</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Type</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Modifiers</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Storage</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Stats</span><span class="gmail-m_8125139665059215853gmail-pln"> target </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Description</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-pun">-----------+------------------<wbr>-----------+------------------<wbr>------------------------------<wbr>---------------+----------+---<wbr>-----------+-------------</span><span class="gmail-m_8125139665059215853gmail-pln">
id </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> integer </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-kwd">not</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-kwd">null</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-kwd">default</span><span class="gmail-m_8125139665059215853gmail-pln"> nextval</span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-str">'flow_times_id_seq'</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-pln">r<wbr>egclass</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> plain </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln">
validtime </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> timestamp without time zone </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> plain </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln">
inittime </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> timestamp without time zone </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> plain </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln">
model </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> character varying</span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-lit">30</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> extended </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-typ">Indexes</span><span class="gmail-m_8125139665059215853gmail-pun">:</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-str">"flow_times_pkey"</span><span class="gmail-m_8125139665059215853gmail-pln"> PRIMARY KEY</span><span class="gmail-m_8125139665059215853gmail-pun">,</span><span class="gmail-m_8125139665059215853gmail-pln"> btree </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">id</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-str">"flow_times_id_idx"</span><span class="gmail-m_8125139665059215853gmail-pln"> btree </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">id</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-str">"flow_times_inittime_idx"</span><span class="gmail-m_8125139665059215853gmail-pln"> btree </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">inittime</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-str">"flow_times_model_idx"</span><span class="gmail-m_8125139665059215853gmail-pln"> btree </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">model</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-str">"flow_times_validtime_idx"</span><span class="gmail-m_8125139665059215853gmail-pln"> btree </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">validtime</span><span class="gmail-m_8125139665059215853gmail-pun">)</span></code></pre>
<p>data table: (10,000 partitions, each with 2.3 million rows)</p>
<pre class="gmail-m_8125139665059215853gmail-default gmail-m_8125139665059215853gmail-prettyprint gmail-m_8125139665059215853gmail-prettyprinted"><code><span class="gmail-m_8125139665059215853gmail-typ">Table</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-str">"public.flow"</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-typ">Column</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Type</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Modifiers</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Storage</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Stats</span><span class="gmail-m_8125139665059215853gmail-pln"> target </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Description</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-pun">------------+---------+-------<wbr>----+---------+--------------+<wbr>-------------</span><span class="gmail-m_8125139665059215853gmail-pln">
station_id </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> integer </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> plain </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln">
timeid </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> integer </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> plain </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln">
streamflow </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> real </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> plain </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln">
velocity </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> real </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> plain </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">|</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-typ">Child</span><span class="gmail-m_8125139665059215853gmail-pln"> tables</span><span class="gmail-m_8125139665059215853gmail-pun">:</span><span class="gmail-m_8125139665059215853gmail-pln"> flow_15192</span><span class="gmail-m_8125139665059215853gmail-pun">,</span><span class="gmail-m_8125139665059215853gmail-pln">
flow_15193</span><span class="gmail-m_8125139665059215853gmail-pun">,</span><span class="gmail-m_8125139665059215853gmail-pln">
flow_15194</span><span class="gmail-m_8125139665059215853gmail-pun">,</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-pun">...</span></code></pre>
<p>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</p>
<p>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.</p>
<pre class="gmail-m_8125139665059215853gmail-default gmail-m_8125139665059215853gmail-prettyprint gmail-m_8125139665059215853gmail-prettyprinted"><code><span class="gmail-m_8125139665059215853gmail-pln">create flow_view SELECT staticflow</span><span class="gmail-m_8125139665059215853gmail-pun">.</span><span class="gmail-m_8125139665059215853gmail-pln">id</span><span class="gmail-m_8125139665059215853gmail-pun">,</span><span class="gmail-m_8125139665059215853gmail-pln">
flow_times</span><span class="gmail-m_8125139665059215853gmail-pun">.</span><span class="gmail-m_8125139665059215853gmail-pln">id AS timeid</span><span class="gmail-m_8125139665059215853gmail-pun">,</span><span class="gmail-m_8125139665059215853gmail-pln">
flow_times</span><span class="gmail-m_8125139665059215853gmail-pun">.</span><span class="gmail-m_8125139665059215853gmail-pln">validtime</span><span class="gmail-m_8125139665059215853gmail-pun">,</span><span class="gmail-m_8125139665059215853gmail-pln">
flow_times</span><span class="gmail-m_8125139665059215853gmail-pun">.</span><span class="gmail-m_8125139665059215853gmail-pln">inittime</span><span class="gmail-m_8125139665059215853gmail-pun">,</span><span class="gmail-m_8125139665059215853gmail-pln">
flow_times</span><span class="gmail-m_8125139665059215853gmail-pun">.</span><span class="gmail-m_8125139665059215853gmail-pln">model</span><span class="gmail-m_8125139665059215853gmail-pun">,</span><span class="gmail-m_8125139665059215853gmail-pln">
staticflow</span><span class="gmail-m_8125139665059215853gmail-pun">.</span><span class="gmail-m_8125139665059215853gmail-pln">strorder</span><span class="gmail-m_8125139665059215853gmail-pun">,</span><span class="gmail-m_8125139665059215853gmail-pln">
flow</span><span class="gmail-m_8125139665059215853gmail-pun">.</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow</span><span class="gmail-m_8125139665059215853gmail-pun">,</span><span class="gmail-m_8125139665059215853gmail-pln">
flow</span><span class="gmail-m_8125139665059215853gmail-pun">.</span><span class="gmail-m_8125139665059215853gmail-pln">velocity</span><span class="gmail-m_8125139665059215853gmail-pun">,</span><span class="gmail-m_8125139665059215853gmail-pln">
staticflow</span><span class="gmail-m_8125139665059215853gmail-pun">.</span><span class="gmail-m_8125139665059215853gmail-pln">shape_length</span><span class="gmail-m_8125139665059215853gmail-pun">,</span><span class="gmail-m_8125139665059215853gmail-pln">
staticflow</span><span class="gmail-m_8125139665059215853gmail-pun">.</span><span class="gmail-m_8125139665059215853gmail-pln">wkb_geometry
FROM staticflow</span><span class="gmail-m_8125139665059215853gmail-pun">,</span><span class="gmail-m_8125139665059215853gmail-pln">
flow_times</span><span class="gmail-m_8125139665059215853gmail-pun">,</span><span class="gmail-m_8125139665059215853gmail-pln">
flow
WHERE flow</span><span class="gmail-m_8125139665059215853gmail-pun">.</span><span class="gmail-m_8125139665059215853gmail-pln">station_id </span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-pln"> staticflow</span><span class="gmail-m_8125139665059215853gmail-pun">.</span><span class="gmail-m_8125139665059215853gmail-pln">id AND flow_times</span><span class="gmail-m_8125139665059215853gmail-pun">.</span><span class="gmail-m_8125139665059215853gmail-pln">id </span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-pln"> flow</span><span class="gmail-m_8125139665059215853gmail-pun">.</span><span class="gmail-m_8125139665059215853gmail-pln">timeid</span><span class="gmail-m_8125139665059215853gmail-pun">;</span></code></pre>
<p>I've looked at the query plan for the queries that GeoServer is sending to Postgres, e.g:</p>
<pre class="gmail-m_8125139665059215853gmail-default gmail-m_8125139665059215853gmail-prettyprint gmail-m_8125139665059215853gmail-prettyprinted"><code><span class="gmail-m_8125139665059215853gmail-pln">explain analyze SELECT </span><span class="gmail-m_8125139665059215853gmail-str">"streamflow"</span><span class="gmail-m_8125139665059215853gmail-pun">,</span><span class="gmail-m_8125139665059215853gmail-pln">encode</span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">ST_<wbr>AsBinary</span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">ST_Simplify</span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">ST_<wbr>Force2D</span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-str">"wkb_geometry"</span><span class="gmail-m_8125139665059215853gmail-pun">),</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">1.0357088025898521E-4</span><span class="gmail-m_8125139665059215853gmail-pun">)),</span><span class="gmail-m_8125139665059215853gmail-str">'<wbr>base64'</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-kwd">as</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-str">"wkb_geometry"</span><span class="gmail-m_8125139665059215853gmail-pln"> FROM </span><span class="gmail-m_8125139665059215853gmail-str">"public"</span><span class="gmail-m_8125139665059215853gmail-pun">.</span><span class="gmail-m_8125139665059215853gmail-str">"flow_view"</span><span class="gmail-m_8125139665059215853gmail-pln"> WHERE </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-str">"timeid"</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">81388</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-str">"timeid"</span><span class="gmail-m_8125139665059215853gmail-pln"> IS NOT NULL AND </span><span class="gmail-m_8125139665059215853gmail-str">"wkb_geometry"</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">&&</span><span class="gmail-m_8125139665059215853gmail-pln"> ST_GeomFromText</span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-str">'POLYGON ((-115.13723373413087 41.01267720039345, -115.13723373413087 41.04660520510985, -115.09225845336915 41.04660520510985, -115.09225845336915 41.01267720039345, -115.13723373413087 41.01267720039345))'</span><span class="gmail-m_8125139665059215853gmail-pun">,</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">4269</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-pun">((</span><span class="gmail-m_8125139665059215853gmail-str">"streamflow"</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">-</span><span class="gmail-m_8125139665059215853gmail-lit">1.0E-4</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-str">"streamflow"</span><span class="gmail-m_8125139665059215853gmail-pln"> IS NOT NULL AND </span><span class="gmail-m_8125139665059215853gmail-str">"streamflow"</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun"><</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">5.0</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> OR </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-str">"streamflow"</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">4.9999</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-str">"streamflow"</span><span class="gmail-m_8125139665059215853gmail-pln"> IS NOT NULL AND </span><span class="gmail-m_8125139665059215853gmail-str">"streamflow"</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun"><</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">10.0</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> OR </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-str">"streamflow"</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">9.9999</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-str">"streamflow"</span><span class="gmail-m_8125139665059215853gmail-pln"> IS NOT NULL AND </span><span class="gmail-m_8125139665059215853gmail-str">"streamflow"</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun"><</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">15.0</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> OR </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-str">"streamflow"</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">14.9999</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-str">"streamflow"</span><span class="gmail-m_8125139665059215853gmail-pln"> IS NOT NULL AND </span><span class="gmail-m_8125139665059215853gmail-str">"streamflow"</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun"><</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">20.0</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> OR </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-str">"streamflow"</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">19.9999</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-str">"streamflow"</span><span class="gmail-m_8125139665059215853gmail-pln"> IS NOT NULL AND </span><span class="gmail-m_8125139665059215853gmail-str">"streamflow"</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun"><</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">25.0</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> OR </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-str">"streamflow"</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">24.9999</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-str">"streamflow"</span><span class="gmail-m_8125139665059215853gmail-pln"> IS NOT NULL AND </span><span class="gmail-m_8125139665059215853gmail-str">"streamflow"</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun"><</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">30.0</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> OR </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-str">"streamflow"</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">29.9999</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-str">"streamflow"</span><span class="gmail-m_8125139665059215853gmail-pln"> IS NOT NULL AND </span><span class="gmail-m_8125139665059215853gmail-str">"streamflow"</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun"><</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">100000.0</span><span class="gmail-m_8125139665059215853gmail-pun">)));</span><span class="gmail-m_8125139665059215853gmail-pln">
QUERY PLAN
</span><span class="gmail-m_8125139665059215853gmail-pun">------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>--</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-typ">Nested</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Loop</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">cost</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">0.70</span><span class="gmail-m_8125139665059215853gmail-pun">..</span><span class="gmail-m_8125139665059215853gmail-lit">163.24</span><span class="gmail-m_8125139665059215853gmail-pln"> rows</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">11</span><span class="gmail-m_8125139665059215853gmail-pln"> width</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">465</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">actual time</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">0.101</span><span class="gmail-m_8125139665059215853gmail-pun">..</span><span class="gmail-m_8125139665059215853gmail-lit">0.200</span><span class="gmail-m_8125139665059215853gmail-pln"> rows</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">9</span><span class="gmail-m_8125139665059215853gmail-pln"> loops</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">1</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-pun">-></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Index</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Only</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Scan</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-kwd">using</span><span class="gmail-m_8125139665059215853gmail-pln"> conus_flow_times_id_idx on conus_flow_times </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">cost</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">0.29</span><span class="gmail-m_8125139665059215853gmail-pun">..</span><span class="gmail-m_8125139665059215853gmail-lit">8.31</span><span class="gmail-m_8125139665059215853gmail-pln"> rows</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">1</span><span class="gmail-m_8125139665059215853gmail-pln"> width</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">4</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">actual time</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">0.020</span><span class="gmail-m_8125139665059215853gmail-pun">..</span><span class="gmail-m_8125139665059215853gmail-lit">0.020</span><span class="gmail-m_8125139665059215853gmail-pln"> rows</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">1</span><span class="gmail-m_8125139665059215853gmail-pln"> loops</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">1</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-typ">Index</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Cond</span><span class="gmail-m_8125139665059215853gmail-pun">:</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">((</span><span class="gmail-m_8125139665059215853gmail-pln">id IS NOT NULL</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">id </span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">81388</span><span class="gmail-m_8125139665059215853gmail-pun">))</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-typ">Heap</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Fetches</span><span class="gmail-m_8125139665059215853gmail-pun">:</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">1</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-pun">-></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Nested</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Loop</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">cost</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">0.41</span><span class="gmail-m_8125139665059215853gmail-pun">..</span><span class="gmail-m_8125139665059215853gmail-lit">154.71</span><span class="gmail-m_8125139665059215853gmail-pln"> rows</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">11</span><span class="gmail-m_8125139665059215853gmail-pln"> width</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">469</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">actual time</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">0.045</span><span class="gmail-m_8125139665059215853gmail-pun">..</span><span class="gmail-m_8125139665059215853gmail-lit">0.104</span><span class="gmail-m_8125139665059215853gmail-pln"> rows</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">9</span><span class="gmail-m_8125139665059215853gmail-pln"> loops</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">1</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-pun">-></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Index</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Scan</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-kwd">using</span><span class="gmail-m_8125139665059215853gmail-pln"> staticflow_wkb_geometry_geom_<wbr>idx on staticflow </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">cost</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">0.41</span><span class="gmail-m_8125139665059215853gmail-pun">..</span><span class="gmail-m_8125139665059215853gmail-lit">52.62</span><span class="gmail-m_8125139665059215853gmail-pln"> rows</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">12</span><span class="gmail-m_8125139665059215853gmail-pln"> width</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">465</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">actual time</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">0.026</span><span class="gmail-m_8125139665059215853gmail-pun">..</span><span class="gmail-m_8125139665059215853gmail-lit">0.058</span><span class="gmail-m_8125139665059215853gmail-pln"> rows</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">9</span><span class="gmail-m_8125139665059215853gmail-pln"> loops</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">1</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-typ">Index</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Cond</span><span class="gmail-m_8125139665059215853gmail-pun">:</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">wkb_geometry </span><span class="gmail-m_8125139665059215853gmail-pun">&&</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-str">'<wbr>0103000020AD100000010000000500<wbr>000001000070C8C85CC01F8C10689F<wbr>81444001000070C8C85CC095E2CB28<wbr>F785444001000090E7C55CC095E2CB<wbr>28F785444001000090E7C55CC01F8C<wbr>10689F81444001000070C8C85CC01F<wbr>8C10689F814440'</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-pln">geometry</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-pun">-></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Append</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">cost</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">0.00</span><span class="gmail-m_8125139665059215853gmail-pun">..</span><span class="gmail-m_8125139665059215853gmail-lit">8.49</span><span class="gmail-m_8125139665059215853gmail-pln"> rows</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">2</span><span class="gmail-m_8125139665059215853gmail-pln"> width</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">12</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">actual time</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">0.004</span><span class="gmail-m_8125139665059215853gmail-pun">..</span><span class="gmail-m_8125139665059215853gmail-lit">0.005</span><span class="gmail-m_8125139665059215853gmail-pln"> rows</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">1</span><span class="gmail-m_8125139665059215853gmail-pln"> loops</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">9</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-pun">-></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Seq</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Scan</span><span class="gmail-m_8125139665059215853gmail-pln"> on conus_flow </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">cost</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">0.00</span><span class="gmail-m_8125139665059215853gmail-pun">..</span><span class="gmail-m_8125139665059215853gmail-lit">0.00</span><span class="gmail-m_8125139665059215853gmail-pln"> rows</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">1</span><span class="gmail-m_8125139665059215853gmail-pln"> width</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">12</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">actual time</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">0.000</span><span class="gmail-m_8125139665059215853gmail-pun">..</span><span class="gmail-m_8125139665059215853gmail-lit">0.000</span><span class="gmail-m_8125139665059215853gmail-pln"> rows</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">0</span><span class="gmail-m_8125139665059215853gmail-pln"> loops</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">9</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-typ">Filter</span><span class="gmail-m_8125139665059215853gmail-pun">:</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">((</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow IS NOT NULL</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">timeid </span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">81388</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">staticflow</span><span class="gmail-m_8125139665059215853gmail-pun">.</span><span class="gmail-m_8125139665059215853gmail-pln">id </span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-pln"> station_id</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-pun">(((</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun">></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">(-</span><span class="gmail-m_8125139665059215853gmail-lit">0.0001</span><span class="gmail-m_8125139665059215853gmail-pun">)::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun"><</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">5</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">))</span><span class="gmail-m_8125139665059215853gmail-pln"> OR </span><span class="gmail-m_8125139665059215853gmail-pun">((</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun">></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">4.9999</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun"><</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">10</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">))</span><span class="gmail-m_8125139665059215853gmail-pln"> OR </span><span class="gmail-m_8125139665059215853gmail-pun">((</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun">></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">9.9999</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun"><</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">15</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">))</span><span class="gmail-m_8125139665059215853gmail-pln"> OR </span><span class="gmail-m_8125139665059215853gmail-pun">((</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun">></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">14.9999</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun"><</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">20</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">))</span><span class="gmail-m_8125139665059215853gmail-pln"> OR </span><span class="gmail-m_8125139665059215853gmail-pun">((</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun">></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">19.9999</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun"><</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">25</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">))</span><span class="gmail-m_8125139665059215853gmail-pln"> OR </span><span class="gmail-m_8125139665059215853gmail-pun">((</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun">></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">24.9999</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun"><</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">30</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">))</span><span class="gmail-m_8125139665059215853gmail-pln"> OR </span><span class="gmail-m_8125139665059215853gmail-pun">((</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun">></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">29.9999</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun"><</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">100000</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">))))</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-pun">-></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Index</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Scan</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-kwd">using</span><span class="gmail-m_8125139665059215853gmail-pln"> conus_flow_81388_station_id_<wbr>idx on conus_flow_81388 </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">cost</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">0.43</span><span class="gmail-m_8125139665059215853gmail-pun">..</span><span class="gmail-m_8125139665059215853gmail-lit">8.49</span><span class="gmail-m_8125139665059215853gmail-pln"> rows</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">1</span><span class="gmail-m_8125139665059215853gmail-pln"> width</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">12</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">actual time</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">0.004</span><span class="gmail-m_8125139665059215853gmail-pun">..</span><span class="gmail-m_8125139665059215853gmail-lit">0.004</span><span class="gmail-m_8125139665059215853gmail-pln"> rows</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">1</span><span class="gmail-m_8125139665059215853gmail-pln"> loops</span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-lit">9</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-typ">Index</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-typ">Cond</span><span class="gmail-m_8125139665059215853gmail-pun">:</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">station_id </span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-pln"> staticflow</span><span class="gmail-m_8125139665059215853gmail-pun">.</span><span class="gmail-m_8125139665059215853gmail-pln">id</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-typ">Filter</span><span class="gmail-m_8125139665059215853gmail-pun">:</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">((</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow IS NOT NULL</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">timeid </span><span class="gmail-m_8125139665059215853gmail-pun">=</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">81388</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-pun">(((</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun">></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-pun">(-</span><span class="gmail-m_8125139665059215853gmail-lit">0.0001</span><span class="gmail-m_8125139665059215853gmail-pun">)::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun"><</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">5</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">))</span><span class="gmail-m_8125139665059215853gmail-pln"> OR </span><span class="gmail-m_8125139665059215853gmail-pun">((</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun">></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">4.9999</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun"><</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">10</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">))</span><span class="gmail-m_8125139665059215853gmail-pln"> OR </span><span class="gmail-m_8125139665059215853gmail-pun">((</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun">></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">9.9999</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun"><</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">15</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">))</span><span class="gmail-m_8125139665059215853gmail-pln"> OR </span><span class="gmail-m_8125139665059215853gmail-pun">((</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun">></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">14.9999</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun"><</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">20</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">))</span><span class="gmail-m_8125139665059215853gmail-pln"> OR </span><span class="gmail-m_8125139665059215853gmail-pun">((</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun">></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">19.9999</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun"><</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">25</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">))</span><span class="gmail-m_8125139665059215853gmail-pln"> OR </span><span class="gmail-m_8125139665059215853gmail-pun">((</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun">></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">24.9999</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun"><</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">30</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">))</span><span class="gmail-m_8125139665059215853gmail-pln"> OR </span><span class="gmail-m_8125139665059215853gmail-pun">((</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun">></span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">29.9999</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">)</span><span class="gmail-m_8125139665059215853gmail-pln"> AND </span><span class="gmail-m_8125139665059215853gmail-pun">(</span><span class="gmail-m_8125139665059215853gmail-pln">streamflow </span><span class="gmail-m_8125139665059215853gmail-pun"><</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">100000</span><span class="gmail-m_8125139665059215853gmail-pun">::</span><span class="gmail-m_8125139665059215853gmail-kwd">double</span><span class="gmail-m_8125139665059215853gmail-pln"> precision</span><span class="gmail-m_8125139665059215853gmail-pun">))))</span><span class="gmail-m_8125139665059215853gmail-pln">
</span><span class="gmail-m_8125139665059215853gmail-typ">Planning</span><span class="gmail-m_8125139665059215853gmail-pln"> time</span><span class="gmail-m_8125139665059215853gmail-pun">:</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">3041.998</span><span class="gmail-m_8125139665059215853gmail-pln"> ms
</span><span class="gmail-m_8125139665059215853gmail-typ">Execution</span><span class="gmail-m_8125139665059215853gmail-pln"> time</span><span class="gmail-m_8125139665059215853gmail-pun">:</span><span class="gmail-m_8125139665059215853gmail-pln"> </span><span class="gmail-m_8125139665059215853gmail-lit">1.192</span><span class="gmail-m_8125139665059215853gmail-pln"> ms</span></code></pre>
<p>(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.)</p>
<p>My questions are</p>
<ol><li>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?</li><li>Is there a way to optimize GeoServer's queries against this schema, or a more efficient query to try?</li><li>other postgres optimizations that might help?</li></ol>
<p>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.</p><p>Thanks for any insight!</p>-Andy</div>