Re: disable seqscan

On Mon, May 23, 2011 at 7:35 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Nick Raj <nickrajjain(at)gmail(dot)com> writes:
> >> Andrew Sullivan <ajs(at)crankycanuck(dot)ca> 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?