Hi Giuseppe, Thanks a lot for your quick response.
Regards, PostgAnn. On Fri, May 22, 2020 at 2:20 PM Giuseppe Broccolo <g.broccol...@gmail.com> wrote: > 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 < > postgann2...@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@lists.osgeo.org >> https://lists.osgeo.org/mailman/listinfo/postgis-users > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users