using indexes

Sferacarta Software wrote:

Hello all,

I have a table SP with a primary key SNO,
I'm trying if v6.4beta2 uses indexes
but seems it uses indexes only with '=' operator:

regression=> explain select unique1 from onek where unique1 < 3;
NOTICE: QUERY PLAN:

Index Scan using onek_unique1 on onek (cost=28.67 size=334 width=4)

prova=> explain select * from sp where sno = 'S1';
NOTICE: QUERY PLAN:
Index Scan using sp_pkey on sp (cost=0.00 size=0 width=28)
EXPLAIN

Looks like maybe you need to vacuum to get statistics for the optimizer?

- Tom

Hi Tom,

TGL> Jose' wrote:

Hello all,

I have a table SP with a primary key SNO,
I'm trying if v6.4beta2 uses indexes
but seems it uses indexes only with '=' operator:

regression=>> explain select unique1 from onek where unique1 < 3;
TGL> NOTICE: QUERY PLAN:

TGL> Index Scan using onek_unique1 on onek (cost=28.67 size=334 width=4)

prova=> explain select * from sp where sno = 'S1';
NOTICE: QUERY PLAN:
Index Scan using sp_pkey on sp (cost=0.00 size=0 width=28)
EXPLAIN

TGL> Looks like maybe you need to vacuum to get statistics for the optimizer?

TGL> - Tom

After vacuum the behavior is the same:

prova=> create table onek(unique1 int4 primary key);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index onek_pkey
for table onek
CREATE
prova=> insert into onek values (1);
INSERT 152858 1
prova=> insert into onek values (10);
INSERT 152859 1
prova=> explain select unique1 from onek where unique1 < 3;
NOTICE: QUERY PLAN:

Seq Scan on onek (cost=0.00 size=0 width=4)

EXPLAIN
prova=> vacuum;
VACUUM
prova=> explain select unique1 from onek where unique1 < 3;
NOTICE: QUERY PLAN:

Seq Scan on onek (cost=1.07 size=1 width=4)

EXPLAIN

PS:
What about:
select unique1 from onek where unique1 = 3 OR unique1 = 1;
^^
does it works on v6.4 ?
Jose'

PS:
What about:
select unique1 from onek where unique1 = 3 OR unique1 = 1;
^^
does it works on v6.4 ?

It should.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian wrote:

PS:
What about:
select unique1 from onek where unique1 = 3 OR unique1 = 1;
^^
does it works on v6.4 ?

It should.

This is a great new.
This thing is absolutely neccessary to have better performances using
ODBC and ACCESS.

Thank you
Jose'

On Mon, 19 Oct 1998, Sferacarta Software wrote:

Hi Tom,

TGL> Jose' wrote:

Hello all,

I have a table SP with a primary key SNO,
I'm trying if v6.4beta2 uses indexes
but seems it uses indexes only with '=' operator:

regression=>> explain select unique1 from onek where unique1 < 3;
TGL> NOTICE: QUERY PLAN:

TGL> Index Scan using onek_unique1 on onek (cost=28.67 size=334 width=4)

prova=> explain select * from sp where sno = 'S1';
NOTICE: QUERY PLAN:
Index Scan using sp_pkey on sp (cost=0.00 size=0 width=28)
EXPLAIN

TGL> Looks like maybe you need to vacuum to get statistics for the optimizer?

TGL> - Tom

After vacuum the behavior is the same:

prova=> create table onek(unique1 int4 primary key);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index onek_pkey
for table onek
CREATE
prova=> insert into onek values (1);
INSERT 152858 1
prova=> insert into onek values (10);
INSERT 152859 1
prova=> explain select unique1 from onek where unique1 < 3;
NOTICE: QUERY PLAN:

Seq Scan on onek (cost=0.00 size=0 width=4)

The table has two records in it...why would it use an index?

Marc G. Fournier scrappy@hub.org
Systems Administrator @ hub.org
scrappy@{postgresql|isc}.org ICQ#7615664

Bruce Momjian wrote:

PS:
What about:
select unique1 from onek where unique1 = 3 OR unique1 = 1;
^^
does it works on v6.4 ?

It should.

This is a great new.
This thing is absolutely neccessary to have better performances using
ODBC and ACCESS.

Yes. We worked hard on this because it was a much needed feature.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026