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

[postgann2020 s]

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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200522/213c6107/attachment.html>