Обсуждение: using indexes

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'

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,
Pennsylvania19026
 

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,
Pennsylvania19026