[Geoserver-users] re peated query?

GeoServer GeoServer User
1

I am trying to get GeoServer to work as a WMS against a rather large dataset
stored with PostGIS, but I am having performance issues.

In my database's slow query log, I see the same query multiple times for a
single request. The SLD specifies an attribute to filter on, but I don't see
this attribute as part of the query. What can I do to get the attribute in
the query's WHERE clause and to not have the query repeat (or at least not
as often)?

Here is the portion of my SLD which applies for the zoom level of the
request:

        <Rule>
          <ogc:Filter>
            <ogc:PropertyIsEqualTo>
                 <ogc:PropertyName>rtetyp</ogc:PropertyName>
                <ogc:Literal>1</ogc:Literal>
              </ogc:PropertyIsEqualTo>
          </ogc:Filter>
          <MinScaleDenominator>3e6</MinScaleDenominator>
          <LineSymbolizer>
...
          </LineSymbolizer>
        </Rule>
        <Rule>
          <ogc:Filter>
            <ogc:And>
              <ogc:PropertyIsEqualTo>
                <ogc:PropertyName>rtetyp</ogc:PropertyName>
                <ogc:Literal>1</ogc:Literal>
              </ogc:PropertyIsEqualTo>
             
<ogc:Not><ogc:PropertyIsNull><ogc:PropertyName>routenum</ogc:PropertyName></ogc:PropertyIsNull></ogc:Not>
              <ogc:PropertyIsGreaterThan>
                <ogc:Function
name="strLength"><ogc:PropertyName>routenum</ogc:PropertyName></ogc:Function>
                <ogc:Literal>2</ogc:Literal>
              </ogc:PropertyIsGreaterThan>
            </ogc:And>
          </ogc:Filter>
          <MinScaleDenominator>3e6</MinScaleDenominator>
          <TextSymbolizer>
...
          </TextSymbolizer>
        </Rule>
      </FeatureTypeStyle>

Here are 2 lines of the 22 for this query in my query log for a particular
request.

2008-03-18 13:27:05 EDT LOG: duration: 5375.000 ms execute S_2/C_3: SELECT
"routenum", "rtetyp", encode(asBinary(force_2d("the_geom"),'XDR'),'base64')
FROM "public"."roads_interstates" WHERE "the_geom" &&
GeometryFromText('POLYGON ((-171.35586639667966 13.114159881152343,
-171.35586639667966 57.16561011884765, -54.532196603320315
57.16561011884765, -54.532196603320315 13.114159881152343,
-171.35586639667966 13.114159881152343))', -1)
2008-03-18 13:27:10 EDT LOG: duration: 3515.000 ms execute fetch from
S_2/C_3: SELECT "routenum", "rtetyp",
encode(asBinary(force_2d("the_geom"),'XDR'),'base64') FROM
"public"."roads_interstates" WHERE "the_geom" && GeometryFromText('POLYGON
((-171.35586639667966 13.114159881152343, -171.35586639667966
57.16561011884765, -54.532196603320315 57.16561011884765,
-54.532196603320315 13.114159881152343, -171.35586639667966
13.114159881152343))', -1)

TIA,
Craig

--
View this message in context: http://www.nabble.com/repeated-query--tp16127454p16127454.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

2

geoman ha scritto:

I am trying to get GeoServer to work as a WMS against a rather large dataset
stored with PostGIS, but I am having performance issues.

In my database's slow query log, I see the same query multiple times for a
single request.

This is very odd, the renderer is coded to make a single request (more on this later).

The SLD specifies an attribute to filter on, but I don't see
this attribute as part of the query. What can I do to get the attribute in
the query's WHERE clause and to not have the query repeat (or at least not
as often)?

Here is the portion of my SLD which applies for the zoom level of the
request:

        <Rule>
          <ogc:Filter>
            <ogc:PropertyIsEqualTo>
                 <ogc:PropertyName>rtetyp</ogc:PropertyName>
                <ogc:Literal>1</ogc:Literal>
              </ogc:PropertyIsEqualTo>
          </ogc:Filter>
          <MinScaleDenominator>3e6</MinScaleDenominator>
          <LineSymbolizer>
...
          </LineSymbolizer>
        </Rule>
        <Rule>
          <ogc:Filter>
            <ogc:And>
              <ogc:PropertyIsEqualTo>
                <ogc:PropertyName>rtetyp</ogc:PropertyName>
                <ogc:Literal>1</ogc:Literal>
              </ogc:PropertyIsEqualTo>
             <ogc:Not><ogc:PropertyIsNull><ogc:PropertyName>routenum</ogc:PropertyName></ogc:PropertyIsNull></ogc:Not>
              <ogc:PropertyIsGreaterThan>
                <ogc:Function
name="strLength"><ogc:PropertyName>routenum</ogc:PropertyName></ogc:Function>
                <ogc:Literal>2</ogc:Literal>
              </ogc:PropertyIsGreaterThan>
            </ogc:And>
          </ogc:Filter>
          <MinScaleDenominator>3e6</MinScaleDenominator>
          <TextSymbolizer>
...
          </TextSymbolizer>
        </Rule>
      </FeatureTypeStyle>

Hum, how many rules are there in your style? Only these two?
The renderer is coded to make just one query against the datastore, to
do so it ORs all the filter expressed in the active rules, provided they
are not too many. In GeoServer 1.5.x I think the limit was like 5 filters, and I elevated it to 20 for 1.6.x. If the number of filters
are too many, inseead of ORing them all into a huge filter condition,
none is sent to the DB and filtering is done in memory.
But when they are not too many, what's hitting your dbms should be like:

select ...
from table
where geom intersects bbox
and (f1 or f2 or f3 or ....)

whilst if the filters are too many it becomes

select ...
from table
where geom intersects bbox

Here are 2 lines of the 22 for this query in my query log for a particular
request.

2008-03-18 13:27:05 EDT LOG: duration: 5375.000 ms execute S_2/C_3: SELECT
"routenum", "rtetyp", encode(asBinary(force_2d("the_geom"),'XDR'),'base64')
FROM "public"."roads_interstates" WHERE "the_geom" &&
GeometryFromText('POLYGON ((-171.35586639667966 13.114159881152343,
-171.35586639667966 57.16561011884765, -54.532196603320315
57.16561011884765, -54.532196603320315 13.114159881152343,
-171.35586639667966 13.114159881152343))', -1)
2008-03-18 13:27:10 EDT LOG: duration: 3515.000 ms execute fetch from
S_2/C_3: SELECT "routenum", "rtetyp",
encode(asBinary(force_2d("the_geom"),'XDR'),'base64') FROM
"public"."roads_interstates" WHERE "the_geom" && GeometryFromText('POLYGON
((-171.35586639667966 13.114159881152343, -171.35586639667966
57.16561011884765, -54.532196603320315 57.16561011884765,
-54.532196603320315 13.114159881152343, -171.35586639667966
13.114159881152343))', -1)

This is really strange. Are you sure you're not using a tiled client? In that case a request is made for each tile...

Cheers
Andrea