Thread
-
using indexes
jose' soares <sferac@bo.nettuno.it> — 1998-10-19T11:09:58Z
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'
-
Re: [HACKERS] using indexes
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-10-19T14:46:59Z
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 -
Re[2]: [HACKERS] using indexes
jose' soares <sferac@bo.nettuno.it> — 1998-10-19T16:12:49Z
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' -
Re: Re[2]: [HACKERS] using indexes
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-10-19T17:07:13Z
> 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
-
Re: [HACKERS] using indexes
Jose Soares <jose@sferacarta.com> — 1998-10-20T09:47:50Z
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'
-
Re: Re[2]: [HACKERS] using indexes
Marc G. Fournier <scrappy@hub.org> — 1998-10-20T11:26:09Z
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 -
Re: [HACKERS] using indexes
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-10-20T16:46:34Z
> 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