[postgis-users] Suggestion to improve geo server query performance

Fri May 22 01:49:57 PDT 2020

Hi PostgAnn,

Why do you think bitmap heap scan is an issue? Checking the EXPLAIN ANALYSE
of the query, looks like the planner uses
the proper plan to execute the query (the order of magnitude of expected
fetched rows and actual fetched after the execution
is mostly the same). In fact, the index is used: the two phases you see,
BitmapHeapScan + BitmapIndexScan, it's because the
planner builds a bitmap in the HEAP of the index blocks that are scanned
multiple times for query execution, and than the index
scan is made through this bitmap: it's a way to improve the performance of
the index, and it is commonly used by GiST indexes
for intersects/contains/iscontained spatial operations. It is even expected
that the BitmapHeapScan takes most of the execution
time.

So the execution looks to be the proper one.

Giuseppe.

Il giorno ven 22 mag 2020 alle ore 08:11 postgann2020 s <
postgann2020 at gmail.com> ha scritto:

> Hi Team,
>
> Thanks for your support.
>
> We are using below environment:
>
> *Application :*
> =============
> Programming Language : JAVA
> Geoserver
>
> Database Stack:
> ===============
> PostgreSQL : 9.5.15
> Postgis
>
> We have 3 geoserver queries and are getting some performance issues.
> The index has been created on geom column as well.
>
> *The same type of issues found for 3 queries:*
> =================================
> 1. Bitmap scan is happening.
> 2. All Queries, again index condition checked.
>
>
> *******************************************************************************************************************************************************************
> *Query:*
>
> explain analyze SELECT
> "table_name_id","ug_route_sub_type","sw_uid22",encode(ST_AsBinary(ST_Simplify(ST_Force2D("the_geom"),
> 0.026540849041691673, true)),'base64') as "the_geom" FROM
> "schema"."table_name" WHERE  ("the_geom" && ST_GeomFromText('POLYGON
> ((89.91210936248413 -0.0878905905185982, 89.91210936248413
> 41.04621680978718, 135.0878906061956 41.04621680978718, 135.0878906061956
> -0.0878905905185982, 89.91210936248413 -0.0878905905185982))', 4326) AND
> (("ug_route_sub_type" = 'IP1-IRU-Intercity' AND "ug_route_sub_type" IS NOT
> NULL ) OR ("ug_route_sub_type" = 'IP1-IRU-Intracity' AND
> "ug_route_sub_type" IS NOT NULL ) OR ("ug_route_sub_type" = 'IRU-Intracity'
> AND "ug_route_sub_type" IS NOT NULL ) OR ("ug_route_sub_type" =
> 'IRU-Intercity' AND "ug_route_sub_type" IS NOT NULL ) OR
> ("ug_route_sub_type" = 'IP1-Own-Intercity' AND "ug_route_sub_type" IS NOT
> NULL ) OR ("ug_route_sub_type" = 'IP1-Own-Intracity' AND
> "ug_route_sub_type" IS NOT NULL ) OR ("ug_route_sub_type" = 'Own-Intracity'
> AND "ug_route_sub_type" IS NOT NULL ) OR ("ug_route_sub_type" =
> 'Own-Intercity' AND "ug_route_sub_type" IS NOT NULL ) OR
> ("ug_route_sub_type" = 'Own-Intercity-Patch-replacement' AND
> "ug_route_sub_type" IS NOT NULL ) OR ("ug_route_sub_type" =
> 'Own-Intracity-Patch-replacement' AND "ug_route_sub_type" IS NOT NULL ) OR
> ("ug_route_sub_type" = 'Clamping' AND "ug_route_sub_type" IS NOT NULL ) OR
> ("ug_route_sub_type" = 'None' AND "ug_route_sub_type" IS NOT NULL ) OR
> ("ug_route_sub_type" = 'On kerb' AND "ug_route_sub_type" IS NOT NULL ) OR
> ("ug_route_sub_type" = 'Other' AND "ug_route_sub_type" IS NOT NULL ) OR
> ("ug_route_sub_type" = 'Suspend' AND "ug_route_sub_type" IS NOT NULL ) OR
> ("ug_route_sub_type" = 'In Duct Chamber' AND "ug_route_sub_type" IS NOT
> NULL ) OR ("ug_route_sub_type" = '' AND "ug_route_sub_type" IS NOT NULL )
> OR "ug_route_sub_type" IS NULL  OR ("sw_uid22" = 'Overhead' AND "sw_uid22"
> IS NOT NULL  AND "ug_route_sub_type" = 'Own-Intercity' AND
> "ug_route_sub_type" IS NOT NULL ) OR ("sw_uid22" = 'Overhead' AND
> "sw_uid22" IS NOT NULL  AND "ug_route_sub_type" = 'Own-Intracity' AND
> "ug_route_sub_type" IS NOT NULL ) OR ("sw_uid22" = 'Overhead' AND
> "sw_uid22" IS NOT NULL  AND "ug_route_sub_type" =
> 'Own-Intercity-Patch-replacement' AND "ug_route_sub_type" IS NOT NULL ) OR
> ("sw_uid22" = 'Overhead' AND "sw_uid22" IS NOT NULL  AND
> "ug_route_sub_type" = 'Own-Intracity-Patch-replacement' AND
> "ug_route_sub_type" IS NOT NULL )));
>
>
> ******************************************************************************
>
> *Explain Plan:*
>
>  Bitmap Heap Scan on table_name  (cost=2394.70..139217.49 rows=50676
> width=157) (actual time=50.335..535.617 rows=71847 loops=1)
>    Recheck Cond: (the_geom &&
> '0103000020E61000000100000005000000AA8FF2FF5F7A56403B4CE76BFF7FB6BFAA8FF2FF5F7A5640DC47B36EEA8544408BE7F5FFCFE26040DC47B36EEA8544408BE7F5F
> FCFE260403B4CE76BFF7FB6BFAA8FF2FF5F7A56403B4CE76BFF7FB6BF'::geometry)
>    Filter: ((((ug_route_sub_type)::text = 'IP1-IRU-Intercity'::text) AND
> (ug_route_sub_type IS NOT NULL)) OR (((ug_route_sub_type)::text =
> 'IP1-IRU-Intracity'::text) AN
> D (ug_route_sub_type IS NOT NULL)) OR (((ug_route_sub_type)::text =
> 'IRU-Intracity'::text) AND (ug_route_sub_type IS NOT NULL)) OR
> (((ug_route_sub_type)::text = 'IRU-In
> tercity'::text) AND (ug_route_sub_type IS NOT NULL)) OR
> (((ug_route_sub_type)::text = 'IP1-Own-Intercity'::text) AND
> (ug_route_sub_type IS NOT NULL)) OR (((ug_route_sub
> _type)::text = 'IP1-Own-Intracity'::text) AND (ug_route_sub_type IS NOT
> NULL)) OR (((ug_route_sub_type)::text = 'Own-Intracity'::text) AND
> (ug_route_sub_type IS NOT NUL
> L)) OR (((ug_route_sub_type)::text = 'Own-Intercity'::text) AND
> (ug_route_sub_type IS NOT NULL)) OR (((ug_route_sub_type)::text =
> 'Own-Intercity-Patch-replacement'::tex
> t) AND (ug_route_sub_type IS NOT NULL)) OR (((ug_route_sub_type)::text =
> 'Own-Intracity-Patch-replacement'::text) AND (ug_route_sub_type IS NOT
> NULL)) OR (((ug_route_su
> b_type)::text = 'Clamping'::text) AND (ug_route_sub_type IS NOT NULL)) OR
> (((ug_route_sub_type)::text = 'None'::text) AND (ug_route_sub_type IS NOT
> NULL)) OR (((ug_rout
> e_sub_type)::text = 'On kerb'::text) AND (ug_route_sub_type IS NOT NULL))
> OR (((ug_route_sub_type)::text = 'Other'::text) AND (ug_route_sub_type IS
> NOT NULL)) OR (((ug_
> route_sub_type)::text = 'Suspend'::text) AND (ug_route_sub_type IS NOT
> NULL)) OR (((ug_route_sub_type)::text = 'In Duct Chamber'::text) AND
> (ug_route_sub_type IS NOT NU
> LL)) OR (((ug_route_sub_type)::text = ''::text) AND (ug_route_sub_type IS
> NOT NULL)) OR (ug_route_sub_type IS NULL) OR (((sw_uid22)::text =
> 'Overhead'::text) AND (sw_ui
> d22 IS NOT NULL) AND ((ug_route_sub_type)::text = 'Own-Intercity'::text)
> AND (ug_route_sub_type IS NOT NULL)) OR (((sw_uid22)::text =
> 'Overhead'::text) AND (sw_uid22 IS
>  NOT NULL) AND ((ug_route_sub_type)::text = 'Own-Intracity'::text) AND
> (ug_route_sub_type IS NOT NULL)) OR (((sw_uid22)::text = 'Overhead'::text)
> AND (sw_uid22 IS NOT N
> ULL) AND ((ug_route_sub_type)::text =
> 'Own-Intercity-Patch-replacement'::text) AND (ug_route_sub_type IS NOT
> NULL)) OR (((sw_uid22)::text = 'Overhead'::text) AND (sw_ui
> d22 IS NOT NULL) AND ((ug_route_sub_type)::text =
> 'Own-Intracity-Patch-replacement'::text) AND (ug_route_sub_type IS NOT
> NULL)))
>    Heap Blocks: exact=45957
>    ->  Bitmap Index Scan on table_name_the_geom_geo_idx
>  (cost=0.00..2382.03 rows=64216 width=0) (actual time=30.147..30.147
> rows=71847 loops=1)
>          Index Cond: (the_geom &&
> '0103000020E61000000100000005000000AA8FF2FF5F7A56403B4CE76BFF7FB6BFAA8FF2FF5F7A5640DC47B36EEA8544408BE7F5FFCFE26040DC47B36EEA8544408BE
> 7F5FFCFE260403B4CE76BFF7FB6BFAA8FF2FF5F7A56403B4CE76BFF7FB6BF'::geometry)
>  Planning time: 0.906 ms
> * Execution time: 541.423 ms*
> (8 rows)
>
> Could you please suggest a better way to execute the query.
>
> Thanks for your support.
>
> Regards,
> PostgAnn.
> _______________________________________________
> 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/20200522/951d424d/attachment.html>