Why the indexes for the attributes are not being used when GeoServer asks PostGIS?

Question

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?

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 )))

Answer 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

Answer 2

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.