Re: disable seqscan

Nick Raj <nickrajjain(at)gmail(dot)com> wrote:

>
> On Mon, May 23, 2011 at 5:44 PM, Andreas Kretschmer <akretschmer(at)spamfence(dot)net>
> wrote:
>
> Andrew Sullivan <ajs(at)crankycanuck(dot)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°