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:

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

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

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

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

  Jose'



Mon, 19 Oct 1998 13:09:58 +0200
Sferacarta Software <sferac@bo.nettuno.it>
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

Mon, 19 Oct 1998 14:46:59 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] using indexes
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'



Mon, 19 Oct 1998 18:12:49 +0200
Sferacarta Software <sferac@bo.nettuno.it>
Re[2]: [HACKERS] using indexes
> 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

Mon, 19 Oct 1998 13:07:13 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: Re[2]: [HACKERS] using indexes
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'

Tue, 20 Oct 1998 11:47:50 +0200
"Jose' Soares" <jose@sferacarta.com>
Re: [HACKERS] using indexes
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


Tue, 20 Oct 1998 07:26:09 -0400 (EDT)
"Marc G. Fournier" <scrappy@hub.org>
Re: Re[2]: [HACKERS] using indexes
> 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

Tue, 20 Oct 1998 12:46:34 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] using indexes