[postgis-users] Optimizing PostGIS/Geoserver schema for huge dataset

[Andrew Gaydos]

Hi,

The query takes over a minute the first time, then about 3 seconds on
subsequent queries. Some on-the-fly index must need to be constructed the
first time, and it is cached for a period afterwards (though the initial
performance reoccurs after some idle period, like an hour or so).

Yes, in this case it only returns 9 rows, because my bounding box is small.

I am getting 4x4=16 tiles - the requests are done in parallel so it's not a
straight 3x16 seconds calculation.

Ah yes, duplicate indexes! Thanks for catching that! Would that incur a
performance hit on database reads?

Thanks,
-Andy

On Sat, Apr 1, 2017 at 10:28 AM, Andy Colson <andy at squeakycode.net> wrote:

> On 03/31/2017 11:38 AM, Andrew Gaydos wrote:
>
>> Hi,
>>
>>
>> explain analyze SELECT "streamflow",encode(ST_AsBinar
>> y(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)
>>
>> Thanks for any insight!
>>
>> -Andy
>>
>
>
> Does that query really only return 9 rows?
>
> Planning time: 3041.998 ms
>>  Execution time: 1.192 ms
>>
>
> So this query, by itself only takes 3 seconds.
>
> about 10 seconds for all the tiles to be served
>>
> How many tiles does it request?  Math (10/3 = ~3) would tell us 3 tiles,
> but that would be odd.  I'd think 4 or 8 tiles.
>
>
>
>     "flow_times_pkey" PRIMARY KEY, btree (id)
>>     "flow_times_id_idx" btree (id)
>>
>
> You have two indexes on the same field on flow_times.
>
> -Andy
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170403/a77f13c9/attachment.html>