Re: disable seqscan - Mailing list pgsql-general

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°