[postgis-users] Indexes not being used

[Paul Ramsey]

It's running in 1 millisecond, what's the problem? If your tables are 
small, it will ignore the indexes because the cost of sequence scanning 
is lower than the index scan cost.

P

Pedro Doria Meunier wrote:
> Hi all (with a special wink to Regina ;- )
> 
> This is the query not using indices:
> 
> SELECT parish,county,geometry FROM pt_madeira_toponymy as t WHERE 
> parish=upper('se') AND
> 
>   t.geometry && geomfromtext('POINT(-16.9213592631455 
> 32.6437878212273)',4326) AND 
> intersects(geomfromtext('POINT(-16.9213592631455 
> 32.6437878212273)',4326), t.geometry);
> 
> As you can plainly see it checks if a point is inside some polygon.
> 
>  
> 
> EXPLAIN ANALYZE returns this:
> 
> "Seq Scan on pt_madeira_toponymy t  (cost=0.00..3.03 rows=1 width=96) 
> (actual time=1.086..1.278 rows=1 loops=1)"
> 
> "  Filter: ((parish = 'SE'::text) AND (geometry && 
> '0101000020E61000009B135F33DEEB30C0FFDDAAA367524040'::geometry) AND 
> intersects('0101000020E61000009B135F33DEEB30C0FFDDAAA367524040'::geometry, 
> geometry))"
> 
> "Total runtime: 1.312 ms"
> 

> The toponymy has two indices: one for 4326 and another for 32628 using GiST.

> 
> Is the intersects function not using indices at all??

-- 

   Paul Ramsey
   Refractions Research
   http://www.refractions.net
   pramsey at refractions.net
   Phone: 250-383-3022
   Cell: 250-885-0632