1

I am working on a WMS to publish data with GeoServer (2.11.0). Tables come from PostGIS (PostgreSQL 9.6). I have indexed geometries on the tables and i have also indexed attributes which are being used in the SLD.

The SLD is quite simple, this is an example of one rule:

<Rule>
      <Name>Secondary_tunnel_stor-1</Name>
      <ogc:Filter>
        <ogc:And>
          <ogc:PropertyIsEqualTo>
            <ogc:PropertyName>highway</ogc:PropertyName>
            <ogc:Literal>secondary</ogc:Literal>               
          </ogc:PropertyIsEqualTo>
          <ogc:PropertyIsEqualTo>
            <ogc:PropertyName>tunnel</ogc:PropertyName>
            <ogc:Literal>yes</ogc:Literal>               
          </ogc:PropertyIsEqualTo>
        </ogc:And>
      </ogc:Filter>
      <MinScaleDenominator>68001</MinScaleDenominator>
      <MaxScaleDenominator>272000</MaxScaleDenominator>
      <LineSymbolizer>
        <Stroke>
          <CssParameter name="stroke">#666666</CssParameter> 
          <CssParameter name="stroke-width">3</CssParameter>
          <CssParameter name="stroke-dasharray">8 4</CssParameter>
        </Stroke>
      </LineSymbolizer>
      <LineSymbolizer>
        <Stroke>
          <CssParameter name="stroke">#faeae6</CssParameter> 
          <CssParameter name="stroke-width">1.5</CssParameter>
        </Stroke>
      </LineSymbolizer>
    </Rule>

But after panning through the WMS I can see on PostGIS that the only index being used is the geometry index:

Why the indexes for the attributes are not being used?

enter image description here

The query sent from GeoServer to PostGIS is like:

SELECT
"id_pkey",
"natural",
"landuse",
"waterway",
encode(
    ST_AsBinary ( CASE WHEN ST_HasArc ( "the_geom" ) THEN "the_geom" ELSE ST_Simplify ( ST_Force2D ( "the_geom" ), 7803.3542335301645, TRUE ) END ),
    'base64' 
) AS "the_geom" 
FROM
"myschema"."mytable" 
WHERE
(
    "the_geom" && ST_GeomFromText ( 'POLYGON ((11888876.271085668 4251249.343989519, 11888876.271085668 11795995.306196207, 17000073.294047713 11795995.306196207, 17000073.294047713 4251249.343989519, 11888876.271085668 4251249.343989519))', 3857 ) 
    AND ((
            "natural" = 'wood' 
            AND "natural" IS NOT NULL 
            ) 
        OR ( "landuse" = 'forest' AND "landuse" IS NOT NULL ) 
        OR ( "natural" = 'glacier' AND "natural" IS NOT NULL ) 
        OR ( "waterway" = 'glacier' AND "waterway" IS NOT NULL ) 
        OR ( "natural" = 'sand' AND "natural" IS NOT NULL ) 
        OR ( "natural" = 'grassland' AND "natural" IS NOT NULL ) 
        OR ( "landuse" = 'residential' AND "landuse" IS NOT NULL ) 
        OR ( "landuse" = 'commercial' AND "landuse" IS NOT NULL ) 
        OR ( "landuse" = 'retail' AND "landuse" IS NOT NULL ) 
        OR ( "landuse" = 'industrial' AND "landuse" IS NOT NULL ) 
        OR ( "landuse" = 'farmland' AND "landuse" IS NOT NULL ) 
        OR ( "landuse" = 'farmyard' AND "landuse" IS NOT NULL ) 
        OR ( "landuse" = 'orchard' AND "landuse" IS NOT NULL ) 
        OR ( "landuse" = 'vineyard' AND "landuse" IS NOT NULL ) 
        OR ( "landuse" = 'grass' AND "landuse" IS NOT NULL ) 
        OR ( "natural" = 'water' AND "natural" IS NOT NULL ) 
        OR ( "waterway" = 'riverbank' AND "waterway" IS NOT NULL ) 
    OR ( "landuse" = 'reservoir' AND "landuse" IS NOT NULL ) 
OR ( "waterway" = 'canal' AND "waterway" IS NOT NULL )))
1

Ok, after a few tries, i finally got it to work!

What i did was just building an index using several columns and directly based on the query that Geoserver sended to Postgis (I could see it on the Postgres log, but it would be nice to see it from Geoserver directly, is there any way to do so?).

The working index is like this:

CREATE INDEX combined_idx
  ON myschema.mytable
  USING btree
  ("natural" COLLATE pg_catalog."default", landuse COLLATE pg_catalog."default", waterway COLLATE pg_catalog."default")
  WHERE ("natural" = ANY (ARRAY['wood'::text, 'glacier'::text, 'sand'::text, 'grassland'::text, 'water'::text])) OR (landuse = ANY (ARRAY['forest'::text, 'residential'::text, 'commercial'::text, 'retail'::text, 'industrial'::text, 'farmland'::text, 'farmyard'::text, 'orchard'::text, 'vineyard'::text, 'grass'::text])) OR (waterway = ANY (ARRAY['riverbank'::text, 'canal'::text]));

for more info about combined indexes: https://www.postgresql.org/docs/9.6/static/indexes-multicolumn.html

  • turn logging up in GeoServer global settings to Developer and you will see the SQL there – Ian Turton May 16 '17 at 9:19
0

The indices are only used when it is necessary for a query. But you do not have a query that asks for attributes. Only for a region that should be passed to Geoserver. And you are not subsetting/searching further the data within this region. So you actually want to have everything and therefore it is scanned sequential. The SLD definitions are made by Geoserver in the next step, so it doesn´t matter.

  • I took a look on the postgres log file and i saw the query that Geoserver sended to Postgis. And thats why i thought that indexing those attributes could help a lot. You are right, after analyze the query, the only index used is the geometry index and then a Heap scan is done. Have you got any clue on how could i make those indexes useful? maybe changing the SLD? maybe customizing something into Geoserver? – Antonio Armas Díaz May 16 '17 at 6:51
  • 1
    Add the important parts of SQL that GeoServer creates into your question. – user30184 May 16 '17 at 7:05
  • take the SQL GeoServer generates (in the logfile) and run explain on it – Ian Turton May 16 '17 at 8:05
  • I did it and it used only geom index and heap scan – Antonio Armas Díaz May 16 '17 at 8:27
  • please add the SQL and explain to the question – Ian Turton May 16 '17 at 9:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.