Re: disable seqscan - Mailing list pgsql-general
| From | Nick Raj |
|---|---|
| Subject | Re: disable seqscan |
| Date | |
| Msg-id | BANLkTimecxQ7NdW+TdGpbruS=cf_xhK89Q@mail.gmail.com Whole thread Raw |
| In response to | Re: disable seqscan (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: disable seqscan
|
| List | pgsql-general |
On Mon, May 23, 2011 at 7:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
CREATE OR REPLACE FUNCTION ndpoint_overlap(ndpoint, ndpoint) RETURNS bool AS '$libdir/ndpoint','ndpoint_overlap' LANGUAGE C IMMUTABLE STRICT;>>> 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/cubeIndexes can only be used with WHERE conditions that are of the form
>> code (cube datatype).
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 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?
pgsql-general by date: