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

[Bo Victor Thomsen]

Try to reduce the sql to this:

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" = ANY ('IP1-IRU-Intercity',
                                    'IP1-IRU-Intracity',
                                    'IRU-Intracity',
                                    'IRU-Intercity',
                                    'IP1-Own-Intercity',
                                    'IP1-Own-Intracity',
                                    'Own-Intracity',
                                    'Own-Intercity',
'Own-Intercity-Patch-replacement',
'Own-Intracity-Patch-replacement',
                                    'Clamping',
                                    'None',
                                    'On kerb',
                                    'Other',
                                    'Suspend',
                                    'In Duct Chamber',
                                    '') OR
         "ug_route_sub_type" IS NULL)

All the sql where - parts with "IS NOT NULL" is superfluous
All the sql where - parts with field "sw_uid22" is superfluous - they 
will be caught anyway in the "= ANY..." clause

Create an index on field "ug_route_sub_type"


Den 22-05-2020 kl. 09:11 skrev postgann2020 s:

> s been created on geom column as well.

-- 
Med venlig hilsen / Kind regards

Bo Victor Thomsen