[postgis-users] Why won’t my SELECT query use the INDEX?

[Rémi Cura]

For this particular case,
using st_srid(geom) means that the planner have to compute this value for
each line .
(the planner has no way to know that srid is the same for every geometry in
the column, this might not be the case)
You can't use it .
for instance, you could simply use plpgsql to get the srid, and create your
querry directly with the number
(your querry looks like it is fabricated programmatically anyway)
You could also look for this srid value in the geometry_columns view, but
it would be less safe.

lastly you coudl had an index on your geom table casted to srid 0, this way
you could systematically cast your wkt to srid 0. I would call it a pretty
bad idea unless you have lot's of differnt srid and you will have to
translate for other usages anyway

Example :
CREATE INDEX ON my_table USING GIST( st_translate(geom,0) )
SELECT "id",encode(ST_AsEWKB("geom"),'base64') as "geometry"
FROM my_table
WHERE st_translate(geom,0) &&
ST_GeomFromText('POLYGON ((892267.1937422 6148208.34251139,
892500.497129949 6148208.34251139, 892500.497129949 6148009.40012468,
892267.1937422 6148009.40012468, 892267.1937422 6148208.34251139))',0)

Cheers,
Rémi-C

2014-12-16 17:43 GMT+01:00 Rémi Cura <remi.cura at gmail.com>:
>
> Hey,
> no index can come from 2 problems :
>   - your querry can't use the index
>   - your querry could use the index but the planner thinks it's faster to
> do sequential scan.
>
> You can discriminate between the 2 avoiding explicitly sequential scan :
> `SET enable_seqscan TO FALSE`
> (don't forget to turn it back one afterward)
> if you are still not using index, your querry can't use it at all, thus
> there is a problem of definition.
> Cheers,
> Rémi-C
>
> 2014-12-16 17:23 GMT+01:00 Andy Colson <andy at squeakycode.net>:
>>
>> On 12/16/2014 9:18 AM, Casper Børgesen (CABO) wrote:
>>
>>> This seems like one of the most asked questions in the PostgreSQL world,
>>> but I guess I haven’t understood all the answers yet:
>>>
>>> Why won’t my SELECT query use the INDEX I have created for it?
>>>
>>> I have a table with about 18mio rows.
>>>
>>> My SELECT statement looks like this:
>>>
>>> SELECT "id",encode(ST_AsEWKB("geom"),'base64') as "geometry"
>>>
>>> FROM my_table
>>>
>>> WHERE geom &&
>>>
>>> ST_GeomFromText('POLYGON ((892267.1937422 6148208.34251139,
>>> 892500.497129949 6148208.34251139, 892500.497129949 6148009.40012468,
>>> 892267.1937422 6148009.40012468, 892267.1937422 6148208.34251139))',
>>> ST_SRID("geom"))
>>>
>>> The EXPLAIN ANALYZE of the above statement returned this:
>>>
>>> "Seq Scan on my_table  (cost=0.00..4329124.83 rows=1731 width=1700)
>>> (actual time=194785.745..1553525.244 rows=138 loops=1)"
>>>
>>> "  Filter: (geom && st_geomfromtext('POLYGON ((892267.1937422
>>> 6148208.34251139, 892500.497129949 6148208.34251139, 892500.497129949
>>> 6148009.40012468, 892267.1937422 6148009.40012468, 892267.1937422
>>> 6148208.34251139))'::text, st_srid(geom)))"
>>>
>>> "  Rows Removed by Filter: 17311187"
>>>
>>> "Total runtime: 1553525.352 ms"
>>>
>>> The POLYGON described above is located at the outer edge of the 17mio
>>> geometries and the extent is pretty small.
>>>
>>> I have executed a VACUUM ANALYZE to clean up the statistics, which
>>> didn’t seem to improve the results.
>>>
>>> My INDEX has been created like this:
>>>
>>> CREATE INDEX my_table_geom_idx
>>>
>>>    ON my_table
>>>
>>>    USING gist
>>>
>>>    (geom);
>>>
>>> Upon reading up on this issue I have changed the following in my
>>> postgresql.conf:
>>>
>>> random_page_cost = 2.0
>>>
>>> shared_buffers = 512MB
>>>
>>> work_mem = 8MB
>>>
>>> maintenance_work_mem = 256MB
>>>
>>> effective_cache_size = 8GB
>>>
>>> The HARDWARE is a Windows 2012R2 with 12GB ram and SAS HDDs. PostgreSQL
>>> 9.3 x64 and PostGIS 2.1.1.
>>>
>>> Can any ask me the right questions so I can solve my INDEX problem?
>>>
>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>
>>>
>>
>> I doubt changing postgresql.conf options will have any affect.
>>
>> Its the st_srid(geom) call that's a problem.
>>
>> Remove it, or specify the integer value.  The function call messes it up.
>>
>> This should work:
>>
>>
>> explain analyze
>> SELECT "id",encode(ST_AsEWKB("geom"),'base64') as "geometry"
>> FROM my_table
>> WHERE geom &&
>> ST_GeomFromText('POLYGON ((892267.1937422 6148208.34251139,
>> 892500.497129949 6148208.34251139, 892500.497129949 6148009.40012468,
>> 892267.1937422 6148009.40012468, 892267.1937422 6148208.34251139))')
>>
>>
>> -Andy
>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20141216/d6489e4c/attachment.html>