Message-ID: <4EECB57F.3050208@tara-lu.com>
Date: Sat, 17 Dec 2011 10:30:07 -0500
From: Roxanne Reid-Bennett <rox@tara-lu.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1;
 rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: will the planner ever use an index when the condition is <> ?
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

I have a query that used <> against an indexed column. In this
case I can use the reverse and use in or = and get the performance
I need... but "in general"... will the planner ever use an index when 
the related column is compared using <>?

I feel like the answer is no, but wanted to ask.

Roxanne
Postgres Version 8.4.9 PostGIS version 1.5.2


Context for question:

I have the following query:

select *
from op_region opr, yield_segment_info ysi, data_location dl
where opr.op_region_id in
          (select distinct op_region_id
           from yield_point
           where yield > 0
           and area > 0
           and ST_GeometryType(location) <> 'ST_Point'
          )
and ysi.op_region_id = opr.op_region_id
and dl.data_set_id = opr.data_set_id

Yield_Point has 161,575,599 records
where yield >0 and area > 0 has 161,263,193 records,
where ST_GeometryType(location)<> 'ST_Point' has just 231 records

yield_segment_info has 165,929 records
op_region has 566,212 records
data_location has 394,763

All of these have a high volume of insert/delete's.
The tables have recently been vacuum full'd and the indexes reindexed.
[they are under the management of the autovacuum, but we forced a 
cleanup on the chance that things had degraded...]

If I run an explain analyze:

"Nested Loop
     (cost=5068203.00..5068230.31 rows=3 width=225308)
     (actual time=192571.730..193625.728 rows=236 loops=1)"
"->Nested Loop
       (cost=5068203.00..5068219.66 rows=1 width=57329)
       (actual time=192522.573..192786.698 rows=230 loops=1)"
"  ->Nested Loop
	(cost=5068203.00..5068211.36 rows=1 width=57268)
	(actual time=192509.822..192638.446 rows=230 loops=1)"
"    ->HashAggregate
            (cost=5068203.00..5068203.01 rows=1 width=4)
            (actual time=192471.507..192471.682 rows=230 loops=1)"
"       ->Seq Scan on yield_point
               (cost=0.00..5068203.00 rows=1 width=4)
               (actual time=602.174..192471.177 rows=230 loops=1)"
"             Filter: ((yield > 0::double precision) AND
                        (area > 0::double precision) AND
                        (st_geometrytype(location) <> 'ST_Point'::text))"
"    ->Index Scan using op_region_pkey on op_region opr
            (cost=0.00..8.33 rows=1 width=57264)
            (actual time=0.723..0.723 rows=1 loops=230)"
"          Index Cond: (opr.op_region_id = yield_point.op_region_id)"
"  ->Index Scan using yield_segment_info_key on yield_segment_info ysi
        (cost=0.00..8.29 rows=1 width=65)
        (actual time=0.643..0.643 rows=1 loops=230)"
"      Index Cond: (ysi.op_region_id = opr.op_region_id)"
"->Index Scan using data_location_data_set_idx on data_location dl
     (cost=0.00..10.61 rows=3 width=167979)
     (actual time=3.611..3.646 rows=1 loops=230)"
"Index Cond: (dl.data_set_id = opr.data_set_id)"
"Total runtime: 193625.955 ms"

yield_point has the following indexes:
       btree on ST_GeometryType(location)
       gist on location
       btree on op_region_id

I've also tried an index on
       ((yield > 0::double precision) AND (area > 0::double precision) 
AND (st_geometrytype(location) <> 'ST_Point'::text))
... it still goes for the sequential scan.

But if I change it to st_geometrytype(location) = 'ST_Polygon' or
even in ('ST_Polygon','ST_MultiPolygon')

the planner uses the index.

Roxanne