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

[Rémi Cura]

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/0de4b936/attachment.html>