Problem with btree index? 
Author Message
 Problem with btree index?

Hi

I have a table consisting of columns as:

time x y z v
1
2
3
.
.

where x,y,z,time and v are all integers.

The size of table is of the order of 500,000 tuples

I created a btree index on this table and when I query as:

select count(*) from table-name where time>=1 and time<=2000

This executes very fast in say 90 ms.

for a query

select count(*) from table-name where time=3000

This is very slow about 1200 ms

I expect this is due to btree index property that range works better than
exact match.

But if i run the same exact match as:

select count(*) from table-name where time>=3000 and time<=3000

It is very fast 90 ms

Any ideas ..why this is so strangely behaving?

Regards

GAUTAM SHANBHAG



Sat, 27 Sep 2003 16:47:31 GMT
 Problem with btree index?

Hi,

please, run "SET EXPLAIN ON;" before you run
the sql-statements and send us the output of
the "sqexplain.out" file.
I think it's easier to find out the problem
if we'd know the query-path of the optimizer.

Regards,

Stefan

PS: A possible explanation would be the
existance of two different indexes with
"time-column" as first attribute.

Quote:

> Hi

> I have a table consisting of columns as:

> time x y z v
> 1
> 2
> 3
> .
> .

> where x,y,z,time and v are all integers.

> The size of table is of the order of 500,000 tuples

> I created a btree index on this table and when I query as:

> select count(*) from table-name where time>=1 and time<=2000

> This executes very fast in say 90 ms.

> for a query

> select count(*) from table-name where time=3000

> This is very slow about 1200 ms

> I expect this is due to btree index property that range works better than
> exact match.

> But if i run the same exact match as:

> select count(*) from table-name where time>=3000 and time<=3000

> It is very fast 90 ms

> Any ideas ..why this is so strangely behaving?

> Regards

> GAUTAM SHANBHAG

--
Stefan Weideneder

Phone: +49 89/3565478-2 ---------------
--- Fax: +49 89/3565478-3 -------------

-------- http://www.weideneder.de -----



Sat, 27 Sep 2003 19:16:09 GMT
 Problem with btree index?

Hi

Thanks for such a prompt reply..

Heres my SQL output i got from set explain on.

The table schema for newtraj is

time s0x s0y s0val ......... s22x s22y s22val

All are in integer

I created only a btree index on time using:

create index on newtraj(time) using btree;

Note: There is no primary key on the table

Thanks

Regards

GAUTAM SHANBHAG

Quote:

> Hi,

> please, run "SET EXPLAIN ON;" before you run
> the sql-statements and send us the output of
> the "sqexplain.out" file.
> I think it's easier to find out the problem
> if we'd know the query-path of the optimizer.

> Regards,

> Stefan

> PS: A possible explanation would be the
> existance of two different indexes with
> "time-column" as first attribute.


> > Hi

> > I have a table consisting of columns as:

> > time x y z v
> > 1
> > 2
> > 3
> > .
> > .

> > where x,y,z,time and v are all integers.

> > The size of table is of the order of 500,000 tuples

> > I created a btree index on this table and when I query as:

> > select count(*) from table-name where time>=1 and time<=2000

> > This executes very fast in say 90 ms.

> > for a query

> > select count(*) from table-name where time=3000

> > This is very slow about 1200 ms

> > I expect this is due to btree index property that range works better than
> > exact match.

> > But if i run the same exact match as:

> > select count(*) from table-name where time>=3000 and time<=3000

> > It is very fast 90 ms

> > Any ideas ..why this is so strangely behaving?

> > Regards

> > GAUTAM SHANBHAG

> --
> Stefan Weideneder

> Phone: +49 89/3565478-2 ---------------
> --- Fax: +49 89/3565478-3 -------------

> -------- http://www.weideneder.de -----

  sqgss.out
< 1K Download


Sun, 28 Sep 2003 02:29:45 GMT
 Problem with btree index?
Have you run UPDATE STATISTICS ??

Yours
--
Earle A Long (Senior DBA)
SinglePoint Limited


Quote:
> Hi

> Thanks for such a prompt reply..

> Heres my SQL output i got from set explain on.

> The table schema for newtraj is

> time s0x s0y s0val ......... s22x s22y s22val

> All are in integer

> I created only a btree index on time using:

> create index on newtraj(time) using btree;

> Note: There is no primary key on the table

> Thanks

> Regards

> GAUTAM SHANBHAG


> > Hi,

> > please, run "SET EXPLAIN ON;" before you run
> > the sql-statements and send us the output of
> > the "sqexplain.out" file.
> > I think it's easier to find out the problem
> > if we'd know the query-path of the optimizer.

> > Regards,

> > Stefan

> > PS: A possible explanation would be the
> > existance of two different indexes with
> > "time-column" as first attribute.


> > > Hi

> > > I have a table consisting of columns as:

> > > time x y z v
> > > 1
> > > 2
> > > 3
> > > .
> > > .

> > > where x,y,z,time and v are all integers.

> > > The size of table is of the order of 500,000 tuples

> > > I created a btree index on this table and when I query as:

> > > select count(*) from table-name where time>=1 and time<=2000

> > > This executes very fast in say 90 ms.

> > > for a query

> > > select count(*) from table-name where time=3000

> > > This is very slow about 1200 ms

> > > I expect this is due to btree index property that range works better
than
> > > exact match.

> > > But if i run the same exact match as:

> > > select count(*) from table-name where time>=3000 and time<=3000

> > > It is very fast 90 ms

> > > Any ideas ..why this is so strangely behaving?

> > > Regards

> > > GAUTAM SHANBHAG

> > --
> > Stefan Weideneder

> > Phone: +49 89/3565478-2 ---------------
> > --- Fax: +49 89/3565478-3 -------------

> > -------- http://www.weideneder.de -----



Tue, 02 Dec 2003 01:28:43 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Problem with btree index on 7.1.3

2. multi-column btree index for real values

3. Btree index extension question

4. patch to allow btree indices on BYTEA

5. btree-indexing-engine

6. Database/Structures and Indexes - Btree?

7. pctfree setting for BTREE indexes

8. Displaying index BTree

9. btree indexing

10. Corrupt Btree Index

11. Btree Secondary Indexes

12. Btree Index Depth


 
Powered by phpBB® Forum Software