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@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users