Hi Team,
Thanks for your support.
We are using below environment:
Thanks for your support.
We are using below environment:
Application :
Programming Language : JAVA
Geoserver
Database Stack:
PostgreSQL : 9.5.15
Postgis
Programming Language : JAVA
Geoserver
Database Stack:
PostgreSQL : 9.5.15
Postgis
We have 3 geoserver queries and are getting some performance issues after changing the GeoServer queries.I have posted the queries and explain the plans of both the old and new queries.
The same type of issues found for 3 queries:
1. Changed index scan to Bitmap scan.
2. All New Queries, again condition checked.
The same type of issues found for 3 queries:
1. Changed index scan to Bitmap scan.
2. All New Queries, again condition checked.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Old Queriy:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Query No:1
1. No issue while executing query.
2. It is feteching: 38 rows only.
===
EXPLAIN ANALYZE SELECT "underground_route_id","ug_route_sub_type","sw_uid22",encode(ST_AsBinary(ST_Simplify(ST_Force2D("the_geom"), 1.506687768824122E-5, true)),'base64') as "the_geom" FROM "schema"."underground_route" WHERE ("the_geom" && ST_GeomFromText('POLYGON ((77.20637798309326 28.627887618687176, 77.20637798309326 28.632784466413323, 77.21195697784424 28.632784466413323, 77.21195697784424 28.627887618687176, 77.20637798309326 28.627887618687176))', 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 )));
Explan Plan:
============
Index Scan using underground_route_the_geom_geo_idx on underground_route (cost=0.41..41.20 rows=7 width=157) (actual time=0.158..1.010 rows=38 loops=1)
Index Cond: (the_geom && '0103000020E610000001000000050000000000004C354D534022D3333EBDA03C400000004C354D53407BA9AC29FEA13C40000000B4904D53407BA9AC29FEA13C40000000B49
04D534022D3333EBDA03C400000004C354D534022D3333EBDA03C40'::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)))
Planning time: 0.845 ms
Execution time: 1.104 ms
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
New Queries:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Query No:1
==========
1. Issue while executing query ==> Taking long time 541.423 ms
2. It is feteching: 71815 rows.
Query Changes:
==============
a). Changed encode(ST_AsBinary(ST_Simplify(ST_Force2D("the_geom"), 1.506687768824122E-5, true)),'base64') TO encode(ST_AsBinary(ST_Simplify(ST_Force2D("the_geom"), 0.026540849041691673, true))
2). Some geom changes.
Explain Plan Observations:
==========================
1. Bitmap Scan instead of Index scan .
=================================
-> Bitmap Index Scan on underground_route_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)
2. Again recheck cond on new query.
===================================
Bitmap Heap Scan on underground_route (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)
Explain Plan for new query:
==========================
explain analyze SELECT "underground_route_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"."underground_route" 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 )));
Bitmap Heap Scan on underground_route (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 underground_route_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)
************************************************************************************************************************
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Old Queriy:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Query No:2
1. No issue while executing query.
2. It is feteching: None.
EXPLAIN ANALYZE SELECT "building_id","color",encode(ST_AsBinary(ST_Force2D("the_geom")),'base64') as "the_geom" FROM "schema"."building" WHERE ("the_geom" && ST_GeomFromText('POLYGON ((55.94238281250001 21.657428197370628, 55.94238281250001 32.212801068015175, 67.80761718750001 32.212801068015175, 67.80761718750001 21.657428197370628, 55.94238281250001 21.657428197370628))', 4326) AND (("color" = 'RED' AND "color" IS NOT NULL ) OR ("color" = 'GREEN' AND "color" IS NOT NULL ) OR ("color" = 'AMBER' AND "color" IS NOT NULL ) OR ("color" = 'YELLOW' AND "color" IS NOT NULL ) OR ("color" = 'BLACK' AND "color" IS NOT NULL ) OR ("color" = 'BLUE' AND "color" IS NOT NULL ) OR ("color" = 'LIGHTGREEN' AND "color" IS NOT NULL )));
Index Scan using building_the_geom_geo_idx on building (cost=0.28..8.32 rows=1 width=47) (actual time=0.014..0.014 rows=0 loops=1)
Index Cond: (the_geom && '0103000020E6100000010000000500000001000000A0F84B40D22CDF364DA8354001000000A0F84B40EBD6BD103D1B404001000000B0F35040EBD6BD103D1B404001000000B
0F35040D22CDF364DA8354001000000A0F84B40D22CDF364DA83540'::geometry)
Filter: ((color IS NOT NULL) AND (((color)::text = 'RED'::text) OR ((color)::text = 'GREEN'::text) OR ((color)::text = 'AMBER'::text) OR ((color)::text = 'YELLOW'::t
ext) OR ((color)::text = 'BLACK'::text) OR ((color)::text = 'BLUE'::text) OR ((color)::text = 'LIGHTGREEN'::text)))
Planning time: 12.002 ms
Execution time: 0.099 ms
(5 rows)
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
New Queries:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1. issue while executing query ==>
2. It is feteching: None. ==> --1462 rows returned
Query Changes:
==============
1). Some geom changes.
Explain Plan Observations:
==========================
1. Bitmap Scan instead of Index scan .
=================================
-> Bitmap Index Scan on building_the_geom_geo_idx (cost=0.00..49.53 rows=1234 width=0) (actual time=0.560..0.560 rows=1445 loops=1)
Index Cond: (building.the_geom && '0103000020E610000001000000050000001298F2FF4F6C5640B23D1ECDF9AFD3BF1298F2FF4F6C564084A4B7B0199B444057E3F5FFD7E9604084A4B7B019
9B444057E3F5FFD7E96040B23D1ECDF9AFD3BF1298F2FF4F6C5640B23D1ECDF9AFD3BF'::geometry)
2. Again recheck cond on new query.
===================================
Bitmap Heap Scan on schema.building (cost=49.75..2775.12 rows=860 width=47) (actual time=0.923..11.523 rows=1444 loops=1)
Output: building_id, color, encode(st_asbinary(st_force2d(the_geom)), 'base64'::text)
Recheck Cond: (building.the_geom && '0103000020E610000001000000050000001298F2FF4F6C5640B23D1ECDF9AFD3BF1298F2FF4F6C564084A4B7B0199B444057E3F5FFD7E9604084A4B7B0199B44
4057E3F5FFD7E96040B23D1ECDF9AFD3BF1298F2FF4F6C5640B23D1ECDF9AFD3BF'::geometry)
Explain Plan:
-------------
explain analyze verbose SELECT "building_id","color",encode(ST_AsBinary(ST_Force2D("the_geom")),'base64') as "the_geom" FROM "schema"."building" WHERE ("the_geom" && ST_GeomFromText('POLYGON ((89.69238280001471 -0.3076157096010902, 89.69238280001471 41.211721505803354, 135.30761716866502 41.211721505803354, 135.30761716866502 -0.3076157096010902, 89.69238280001471 -0.3076157096010902))', 4326) AND (("color" = 'RED' AND "color" IS NOT NULL ) OR ("color" = 'GREEN' AND "color" IS NOT NULL ) OR ("color" = 'AMBER' AND "color" IS NOT NULL ) OR ("color" = 'YELLOW' AND "color" IS NOT NULL ) OR ("color" = 'BLACK' AND "color" IS NOT NULL ) OR ("color" = 'BLUE' AND "color" IS NOT NULL ) OR ("color" = 'LIGHTGREEN' AND "color" IS NOT NULL )));
Bitmap Heap Scan on schema.building (cost=49.75..2775.12 rows=860 width=47) (actual time=0.923..11.523 rows=1444 loops=1)
Output: building_id, color, encode(st_asbinary(st_force2d(the_geom)), 'base64'::text)
Recheck Cond: (building.the_geom && '0103000020E610000001000000050000001298F2FF4F6C5640B23D1ECDF9AFD3BF1298F2FF4F6C564084A4B7B0199B444057E3F5FFD7E9604084A4B7B0199B44
4057E3F5FFD7E96040B23D1ECDF9AFD3BF1298F2FF4F6C5640B23D1ECDF9AFD3BF'::geometry)
Filter: ((building.color IS NOT NULL) AND (((building.color)::text = 'RED'::text) OR ((building.color)::text = 'GREEN'::text) OR ((building.color)::text = 'AMBER'::t
ext) OR ((building.color)::text = 'YELLOW'::text) OR ((building.color)::text = 'BLACK'::text) OR ((building.color)::text = 'BLUE'::text) OR ((building.color)::text = 'L
IGHTGREEN'::text)))
Rows Removed by Filter: 1
Heap Blocks: exact=1148
-> Bitmap Index Scan on building_the_geom_geo_idx (cost=0.00..49.53 rows=1234 width=0) (actual time=0.560..0.560 rows=1445 loops=1)
Index Cond: (building.the_geom && '0103000020E610000001000000050000001298F2FF4F6C5640B23D1ECDF9AFD3BF1298F2FF4F6C564084A4B7B0199B444057E3F5FFD7E9604084A4B7B019
9B444057E3F5FFD7E96040B23D1ECDF9AFD3BF1298F2FF4F6C5640B23D1ECDF9AFD3BF'::geometry)
Planning time: 0.813 ms
Execution time: 11.785 ms
(10 rows)
******************************************************************************************************************
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Old Queriy:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Query No:3
1. No issue while executing query.
2. It is feteching: 13 rows.
EXPLAIN ANALYZE SELECT "mahole_id","color","u_id15",encode(ST_AsBinary(ST_Force2D("the_geom")),'base64') as "the_geom" FROM "schema"."manhole" WHERE "the_geom" && ST_GeomFromText('POLYGON ((77.22275018692015 28.632614963963334, 77.22275018692015 28.637699918367634, 77.22854375839233 28.637699918367634, 77.22854375839233 28.632614963963334, 77.22275018692015 28.632614963963334))', 4326);
'Index Scan using manhole_the_geom_geo_idx on manhole (cost=0.28..20.38 rows=4 width=51) (actual time=0.056..0.103 rows=13 loops=1)'
' Index Cond: (the_geom && '0103000020E61000000100000005000000FFFFFF89414E5340C82EE50DF3A13C40FFFFFF89414E5340050D464D40A33C4000000076A04E5340050D464D40A33C4000000076A04E5340C82EE50DF3A13C40FFFFFF89414E5340C82EE50DF3A13C40'::geometry)'
'Planning time: 0.266 ms'
'Execution time: 0.132 ms'
============================================
explain analyze SELECT "mahole_id","color","u_id15",encode(ST_AsBinary(ST_Force2D("the_geom")),'base64') as "the_geom" FROM "schema"."manhole" WHERE "the_geom" && ST_GeomFromText('POLYGON ((89.69238280001471 -0.3076157096010902, 89.69238280001471 41.211721505803354, 135.30761716866502 41.211721505803354, 135.30761716866502 -0.3076157096010902, 89.69238280001471 -0.3076157096010902))', 4326);
'Bitmap Heap Scan on manhole (cost=272.70..14311.39 rows=7280 width=51) (actual time=1.956..74.734 rows=7537 loops=1)'
' Recheck Cond: (the_geom && '0103000020E610000001000000050000001298F2FF4F6C5640B23D1ECDF9AFD3BF1298F2FF4F6C564084A4B7B0199B444057E3F5FFD7E9604084A4B7B0199B444057E3F5FFD7E96040B23D1ECDF9AFD3BF1298F2FF4F6C5640B23D1ECDF9AFD3BF'::geometry)'
' Heap Blocks: exact=5512'
' -> Bitmap Index Scan on manhole_the_geom_geo_idx (cost=0.00..270.88 rows=7280 width=0) (actual time=1.181..1.181 rows=7537 loops=1)'
' Index Cond: (the_geom && '0103000020E610000001000000050000001298F2FF4F6C5640B23D1ECDF9AFD3BF1298F2FF4F6C564084A4B7B0199B444057E3F5FFD7E9604084A4B7B0199B444057E3F5FFD7E96040B23D1ECDF9AFD3BF1298F2FF4F6C5640B23D1ECDF9AFD3BF'::geometry)'
'Planning time: 0.287 ms'
'Execution time: 75.180 ms'
--7537 rows returned.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Old Queriy:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Query No:1
1. No issue while executing query.
2. It is feteching: 38 rows only.
===
EXPLAIN ANALYZE SELECT "underground_route_id","ug_route_sub_type","sw_uid22",encode(ST_AsBinary(ST_Simplify(ST_Force2D("the_geom"), 1.506687768824122E-5, true)),'base64') as "the_geom" FROM "schema"."underground_route" WHERE ("the_geom" && ST_GeomFromText('POLYGON ((77.20637798309326 28.627887618687176, 77.20637798309326 28.632784466413323, 77.21195697784424 28.632784466413323, 77.21195697784424 28.627887618687176, 77.20637798309326 28.627887618687176))', 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 )));
Explan Plan:
============
Index Scan using underground_route_the_geom_geo_idx on underground_route (cost=0.41..41.20 rows=7 width=157) (actual time=0.158..1.010 rows=38 loops=1)
Index Cond: (the_geom && '0103000020E610000001000000050000000000004C354D534022D3333EBDA03C400000004C354D53407BA9AC29FEA13C40000000B4904D53407BA9AC29FEA13C40000000B49
04D534022D3333EBDA03C400000004C354D534022D3333EBDA03C40'::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)))
Planning time: 0.845 ms
Execution time: 1.104 ms
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
New Queries:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Query No:1
==========
1. Issue while executing query ==> Taking long time 541.423 ms
2. It is feteching: 71815 rows.
Query Changes:
==============
a). Changed encode(ST_AsBinary(ST_Simplify(ST_Force2D("the_geom"), 1.506687768824122E-5, true)),'base64') TO encode(ST_AsBinary(ST_Simplify(ST_Force2D("the_geom"), 0.026540849041691673, true))
2). Some geom changes.
Explain Plan Observations:
==========================
1. Bitmap Scan instead of Index scan .
=================================
-> Bitmap Index Scan on underground_route_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)
2. Again recheck cond on new query.
===================================
Bitmap Heap Scan on underground_route (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)
Explain Plan for new query:
==========================
explain analyze SELECT "underground_route_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"."underground_route" 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 )));
Bitmap Heap Scan on underground_route (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 underground_route_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)
************************************************************************************************************************
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Old Queriy:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Query No:2
1. No issue while executing query.
2. It is feteching: None.
EXPLAIN ANALYZE SELECT "building_id","color",encode(ST_AsBinary(ST_Force2D("the_geom")),'base64') as "the_geom" FROM "schema"."building" WHERE ("the_geom" && ST_GeomFromText('POLYGON ((55.94238281250001 21.657428197370628, 55.94238281250001 32.212801068015175, 67.80761718750001 32.212801068015175, 67.80761718750001 21.657428197370628, 55.94238281250001 21.657428197370628))', 4326) AND (("color" = 'RED' AND "color" IS NOT NULL ) OR ("color" = 'GREEN' AND "color" IS NOT NULL ) OR ("color" = 'AMBER' AND "color" IS NOT NULL ) OR ("color" = 'YELLOW' AND "color" IS NOT NULL ) OR ("color" = 'BLACK' AND "color" IS NOT NULL ) OR ("color" = 'BLUE' AND "color" IS NOT NULL ) OR ("color" = 'LIGHTGREEN' AND "color" IS NOT NULL )));
Index Scan using building_the_geom_geo_idx on building (cost=0.28..8.32 rows=1 width=47) (actual time=0.014..0.014 rows=0 loops=1)
Index Cond: (the_geom && '0103000020E6100000010000000500000001000000A0F84B40D22CDF364DA8354001000000A0F84B40EBD6BD103D1B404001000000B0F35040EBD6BD103D1B404001000000B
0F35040D22CDF364DA8354001000000A0F84B40D22CDF364DA83540'::geometry)
Filter: ((color IS NOT NULL) AND (((color)::text = 'RED'::text) OR ((color)::text = 'GREEN'::text) OR ((color)::text = 'AMBER'::text) OR ((color)::text = 'YELLOW'::t
ext) OR ((color)::text = 'BLACK'::text) OR ((color)::text = 'BLUE'::text) OR ((color)::text = 'LIGHTGREEN'::text)))
Planning time: 12.002 ms
Execution time: 0.099 ms
(5 rows)
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
New Queries:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1. issue while executing query ==>
2. It is feteching: None. ==> --1462 rows returned
Query Changes:
==============
1). Some geom changes.
Explain Plan Observations:
==========================
1. Bitmap Scan instead of Index scan .
=================================
-> Bitmap Index Scan on building_the_geom_geo_idx (cost=0.00..49.53 rows=1234 width=0) (actual time=0.560..0.560 rows=1445 loops=1)
Index Cond: (building.the_geom && '0103000020E610000001000000050000001298F2FF4F6C5640B23D1ECDF9AFD3BF1298F2FF4F6C564084A4B7B0199B444057E3F5FFD7E9604084A4B7B019
9B444057E3F5FFD7E96040B23D1ECDF9AFD3BF1298F2FF4F6C5640B23D1ECDF9AFD3BF'::geometry)
2. Again recheck cond on new query.
===================================
Bitmap Heap Scan on schema.building (cost=49.75..2775.12 rows=860 width=47) (actual time=0.923..11.523 rows=1444 loops=1)
Output: building_id, color, encode(st_asbinary(st_force2d(the_geom)), 'base64'::text)
Recheck Cond: (building.the_geom && '0103000020E610000001000000050000001298F2FF4F6C5640B23D1ECDF9AFD3BF1298F2FF4F6C564084A4B7B0199B444057E3F5FFD7E9604084A4B7B0199B44
4057E3F5FFD7E96040B23D1ECDF9AFD3BF1298F2FF4F6C5640B23D1ECDF9AFD3BF'::geometry)
Explain Plan:
-------------
explain analyze verbose SELECT "building_id","color",encode(ST_AsBinary(ST_Force2D("the_geom")),'base64') as "the_geom" FROM "schema"."building" WHERE ("the_geom" && ST_GeomFromText('POLYGON ((89.69238280001471 -0.3076157096010902, 89.69238280001471 41.211721505803354, 135.30761716866502 41.211721505803354, 135.30761716866502 -0.3076157096010902, 89.69238280001471 -0.3076157096010902))', 4326) AND (("color" = 'RED' AND "color" IS NOT NULL ) OR ("color" = 'GREEN' AND "color" IS NOT NULL ) OR ("color" = 'AMBER' AND "color" IS NOT NULL ) OR ("color" = 'YELLOW' AND "color" IS NOT NULL ) OR ("color" = 'BLACK' AND "color" IS NOT NULL ) OR ("color" = 'BLUE' AND "color" IS NOT NULL ) OR ("color" = 'LIGHTGREEN' AND "color" IS NOT NULL )));
Bitmap Heap Scan on schema.building (cost=49.75..2775.12 rows=860 width=47) (actual time=0.923..11.523 rows=1444 loops=1)
Output: building_id, color, encode(st_asbinary(st_force2d(the_geom)), 'base64'::text)
Recheck Cond: (building.the_geom && '0103000020E610000001000000050000001298F2FF4F6C5640B23D1ECDF9AFD3BF1298F2FF4F6C564084A4B7B0199B444057E3F5FFD7E9604084A4B7B0199B44
4057E3F5FFD7E96040B23D1ECDF9AFD3BF1298F2FF4F6C5640B23D1ECDF9AFD3BF'::geometry)
Filter: ((building.color IS NOT NULL) AND (((building.color)::text = 'RED'::text) OR ((building.color)::text = 'GREEN'::text) OR ((building.color)::text = 'AMBER'::t
ext) OR ((building.color)::text = 'YELLOW'::text) OR ((building.color)::text = 'BLACK'::text) OR ((building.color)::text = 'BLUE'::text) OR ((building.color)::text = 'L
IGHTGREEN'::text)))
Rows Removed by Filter: 1
Heap Blocks: exact=1148
-> Bitmap Index Scan on building_the_geom_geo_idx (cost=0.00..49.53 rows=1234 width=0) (actual time=0.560..0.560 rows=1445 loops=1)
Index Cond: (building.the_geom && '0103000020E610000001000000050000001298F2FF4F6C5640B23D1ECDF9AFD3BF1298F2FF4F6C564084A4B7B0199B444057E3F5FFD7E9604084A4B7B019
9B444057E3F5FFD7E96040B23D1ECDF9AFD3BF1298F2FF4F6C5640B23D1ECDF9AFD3BF'::geometry)
Planning time: 0.813 ms
Execution time: 11.785 ms
(10 rows)
******************************************************************************************************************
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Old Queriy:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Query No:3
1. No issue while executing query.
2. It is feteching: 13 rows.
EXPLAIN ANALYZE SELECT "mahole_id","color","u_id15",encode(ST_AsBinary(ST_Force2D("the_geom")),'base64') as "the_geom" FROM "schema"."manhole" WHERE "the_geom" && ST_GeomFromText('POLYGON ((77.22275018692015 28.632614963963334, 77.22275018692015 28.637699918367634, 77.22854375839233 28.637699918367634, 77.22854375839233 28.632614963963334, 77.22275018692015 28.632614963963334))', 4326);
'Index Scan using manhole_the_geom_geo_idx on manhole (cost=0.28..20.38 rows=4 width=51) (actual time=0.056..0.103 rows=13 loops=1)'
' Index Cond: (the_geom && '0103000020E61000000100000005000000FFFFFF89414E5340C82EE50DF3A13C40FFFFFF89414E5340050D464D40A33C4000000076A04E5340050D464D40A33C4000000076A04E5340C82EE50DF3A13C40FFFFFF89414E5340C82EE50DF3A13C40'::geometry)'
'Planning time: 0.266 ms'
'Execution time: 0.132 ms'
============================================
explain analyze SELECT "mahole_id","color","u_id15",encode(ST_AsBinary(ST_Force2D("the_geom")),'base64') as "the_geom" FROM "schema"."manhole" WHERE "the_geom" && ST_GeomFromText('POLYGON ((89.69238280001471 -0.3076157096010902, 89.69238280001471 41.211721505803354, 135.30761716866502 41.211721505803354, 135.30761716866502 -0.3076157096010902, 89.69238280001471 -0.3076157096010902))', 4326);
'Bitmap Heap Scan on manhole (cost=272.70..14311.39 rows=7280 width=51) (actual time=1.956..74.734 rows=7537 loops=1)'
' Recheck Cond: (the_geom && '0103000020E610000001000000050000001298F2FF4F6C5640B23D1ECDF9AFD3BF1298F2FF4F6C564084A4B7B0199B444057E3F5FFD7E9604084A4B7B0199B444057E3F5FFD7E96040B23D1ECDF9AFD3BF1298F2FF4F6C5640B23D1ECDF9AFD3BF'::geometry)'
' Heap Blocks: exact=5512'
' -> Bitmap Index Scan on manhole_the_geom_geo_idx (cost=0.00..270.88 rows=7280 width=0) (actual time=1.181..1.181 rows=7537 loops=1)'
' Index Cond: (the_geom && '0103000020E610000001000000050000001298F2FF4F6C5640B23D1ECDF9AFD3BF1298F2FF4F6C564084A4B7B0199B444057E3F5FFD7E9604084A4B7B0199B444057E3F5FFD7E96040B23D1ECDF9AFD3BF1298F2FF4F6C5640B23D1ECDF9AFD3BF'::geometry)'
'Planning time: 0.287 ms'
'Execution time: 75.180 ms'
--7537 rows returned.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Please provide some suggestion on this.
Thanks & Regards,
PostgAnn.
