On Mon, May 23, 2011 at 7:35 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Nick Raj <nickrajj...@gmail.com> writes: > >> Andrew Sullivan <a...@crankycanuck.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?