PostGIS, no use of gist for geometry column in a query

Question

My query is:

SELECT count(datasetid)
FROM scenes
WHERE acquisitiondate between '2014-06-01' and '2015-06-01'
AND ST_Intersects(ST_GeographyFromText('SRID=4326; POLYGON((' || '10.66437' || ' ' || '49.981172' || ', ' || '15.46437' || ' ' || '49.981172' || ', ' || '15.46437' || ' ' || '54.781172' || ', ' || '10.66437' || ' ' || '54.781172' || ', ' || '10.66437' || ' ' || '49.981172' || '))'), bounds)
GROUP BY datasetid;

datasetid has the data type smallint and acquisitiondate is a timestamp (without time zone).

There is a gist-index on the column bounds. The index won't be used if the column has geometry data type. But if the column has geography data type than the gist index is used instead of a sequential scan.

What is the reason for this?

Maybe this can be helpful: PostGIS Version is 2.1.8 and PostgreSQL 9.4.5.

Answer 1

You want both sides of the query to have the same type, for the index to come into play. By turning 'bounds' into geography, you're accomplishing this. You could also do it by using ST_GeomFromText() and turning bounds into a geometry instead, so both sides are geometry.