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

Fri May 22 02:05:25 PDT 2020

Hi Giuseppe,

Thanks a lot for your quick response.

Regards,
PostgAnn.

On Fri, May 22, 2020 at 2:20 PM Giuseppe Broccolo <g.broccolo.7 at 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 <
> 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
>
> _______________________________________________
> 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/1daddb1f/attachment.html>