Re: disable seqscan

[Nick Raj <nickrajjain@xxxxxxxxx>]

On Mon, May 23, 2011 at 7:35 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

Nick Raj <nickrajjain@xxxxxxxxx> writes:
>> Andrew Sullivan <ajs@xxxxxxxxxxxxxxx> wrote:

>>> It sounds like your index can't actually be used to satisfy your
>>> query.  Without seeing the table definition, index definition, and
>>> query, however, it's pretty hard to give you a real answer.

>> explain analyze select * from vehicle_stindex where
>> ndpoint_overlap('(116.4,39.3,2008/02/11 11:11:11),(117.8,39.98,2008/02/13
>> 11:11:11)',stpoint);

>> I have defined a datatype called ndpoint. It works same as contrib/cube
>> code (cube datatype).

Indexes can only be used with WHERE conditions that are of the form
       indexed_column  operator  some_expression
where the operator is one of those belonging to the index's operator
class.  You haven't told us what operators you put into the operator
class for this new data type, but in any case the function
ndpoint_overlap is not one of them.

                       regards, tom lane

    CREATE OR REPLACE FUNCTION ndpoint_overlap(ndpoint, ndpoint) RETURNS bool AS '$libdir/ndpoint','ndpoint_overlap' LANGUAGE C    IMMUTABLE STRICT;

CREATE OPERATOR && (
    LEFTARG = ndpoint, RIGHTARG = ndpoint, PROCEDURE = ndpoint_overlap,
    COMMUTATOR = '&&',
    RESTRICT = areasel, JOIN = areajoinsel
);

CREATE OPERATOR CLASS gist_ndpoint_ops
    DEFAULT FOR TYPE ndpoint USING gist AS
    OPERATOR    3    &&, ..............

One think i am not able to understand is, if i use ndpoint_overlap method it is going for seq. scan every time but if i use && operator it is using index scan. Why it is so?
Look below for their explain analyze statement

1. explain analyze select * from vehicle_stindex where ndpoint_overlap('(116.4,39.3,2008/02/11 11:11:11),(117.8,39.98,2008/02/13 11:11:11)',stpoint);
                                                                    QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on vehicle_stindex  (cost=10000000000.00..10000050870.86 rows=698823 width=66) (actual time=599.300..599.300 rows=0 loops=1)
   Filter: ndpoint_overlap('(116.400000,39.300000,2008-02-11 11:11:11+05:30),(117.800000,39.980000,2008-02-13 11:11:11+05:30)'::ndpoint, stpoint)
 Total runtime: 599.337 ms
(3 rows)

2. explain analyze select * from vehicle_stindex where '(116.4,39.3,2008/02/11 11:11:11),(117.8,39.98,2008/02/13 11:11:11)' && stpoint;
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using stindex on vehicle_stindex  (cost=0.00..58542.00 rows=10482 width=66) (actual time=0.866..0.866 rows=0 loops=1)
   Index Cond: ('(116.400000,39.300000,2008-02-11 11:11:11+05:30),(117.800000,39.980000,2008-02-13 11:11:11+05:30)'::ndpoint && stpoint)
 Total runtime: 0.916 ms
(3 rows)

Why these is happening?