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

[Andy Colson]

On 03/31/2017 11:38 AM, Andrew Gaydos wrote:
> Hi,
>
>
> 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)
>
> 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