Обсуждение: disable seqscan

Hi,
I have build an index. When, i execute the query, it gives the result by sequential scan, not by using my index.
I have already run vacuum analyze to collect some statistics regarding table.

May be sequential scan is giving faster execution time than my indexing. But i want to know how much time it would take in my indexing.
For that, i have set enable_seqscan=off in postgresql.conf. But it still going through sequential scan. Even i tried to set for a particular session, by "set enable_seqscan=off" on psql terminal.
It again going by sequential scan.

Does any one having an idea to force postgres to use index scan?

Thanks
Nick

On Mon, May 23, 2011 at 05:31:04PM +0530, Nick Raj wrote:
> Hi,
> I have build an index. When, i execute the query, it gives the result by
> sequential scan, not by using my index.

> For that, i have set enable_seqscan=off in postgresql.conf. But it still
> going through sequential scan.

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.

A

--
Andrew Sullivan
ajs@crankycanuck.ca

Andrew Sullivan <ajs@crankycanuck.ca> wrote:

> On Mon, May 23, 2011 at 05:31:04PM +0530, Nick Raj wrote:
> > Hi,
> > I have build an index. When, i execute the query, it gives the result by
> > sequential scan, not by using my index.
>
> > For that, i have set enable_seqscan=off in postgresql.conf. But it still
> > going through sequential scan.
>
> 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.

... and the output produced by

explain analyse <insert your query>


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Nick Raj <nickrajjain@gmail.com> wrote:

>
> On Mon, May 23, 2011 at 5:44 PM, Andreas Kretschmer <akretschmer@spamfence.net>
> wrote:
>
>     Andrew Sullivan <ajs@crankycanuck.ca> wrote:
>
>     > On Mon, May 23, 2011 at 05:31:04PM +0530, Nick Raj wrote:
>     > > Hi,
>     > > I have build an index. When, i execute the query, it gives the result
>     by
>     > > sequential scan, not by using my index.
>     >
>     > > For that, i have set enable_seqscan=off in postgresql.conf. But it
>     still
>     > > going through sequential scan.
>     >
>     > 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.
>
>     ... and the output produced by
>
>     explain analyse <insert your query>
>
>     Explain analyze of my query
>     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=3285.106..3285.106 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: 3285.153 ms
>     (3 rows)
>
>
>     Table Defination
>
>     Table "public.vehicle_stindex"
>      Column  |  Type   | Modifiers
>     ---------+---------+-----------
>      regno   | text    |
>      stpoint | ndpoint |
>     Indexes:
>         "stindex" gist (stpoint)
>
>     It has 2099192 tuples.
>
>
>     Index defination
>     create index stindex on vehicle_stindex using gist(stpoint).
>
>
>     I have defined a datatype called ndpoint. It works same as contrib/cube
>     code (cube datatype).
>     Query is working fine. I mean no error from query or my datatype. All are
>     giving right result.
>
>     If anything more to mention, then tell me

Okay. Sorry, i'm not familiar with gist and gist-functions and postgis
and so on, but i think, your index is unuseable in this case.

To use the index you have to build a functional index with this
function, for instance (i'm not sure if this is correct, as i said, i'm
not familiar with this):

create index stindex on vehicle_stindex (ndpoint_overlap(...))

Simple example for an functional index:

test=# create table foo (f text);
CREATE TABLE
Time: 5,555 ms
test=*# create index idx_foo on foo(md5(f));
CREATE INDEX


now you have an index on the md5-sum from foo.f, and you can say:

select * from foo where md5(f) = md5('test')

I think, that's the way you are looking for.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Nick Raj <nickrajjain@gmail.com> writes:
>> Andrew Sullivan <ajs@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?

On 24 May 2011, at 8:22, Nick Raj wrote:
> 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
iuse && operator it is using index scan. Why it is so? 

> Why these is happening?

Tom already explained that, but in short: Because a function is not an operator.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4ddb741511921606159980!