On Mon, May 23, 2011 at 5:44 PM, Andreas Kretschmer <akretschmer@xxxxxxxxxxxxx> wrote:
Andrew Sullivan <ajs@xxxxxxxxxxxxxxx> wrote:... and the output produced by
> 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.
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
