Re: disable seqscan

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

Nick

>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>