I have succeeded in making OR clauses use indexes.  I have not dealt
with some of the more complex issues like x=3 or y=5 as Vadim mentioned,
but at least it works.

	test=> select * from test where x=102532 or x=102533;
	     x
	------
	102532
	102533
	(2 rows)
	
	test=> explain select * from test where x=102532 or x=102533;
	NOTICE:  QUERY PLAN:
	
	Index Scan using i_test on test  (cost=4.10 size=1 width=4)

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Fri, 31 Jul 1998 01:32:51 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
OR clause status report
Bruce Momjian wrote:
> 
> I have succeeded in making OR clauses use indexes.  I have not dealt
> with some of the more complex issues like x=3 or y=5 as Vadim mentioned,
> but at least it works.
> 
>         test=> select * from test where x=102532 or x=102533;

But did you care about the case when two indices (on test(x) and
on test(y)) exist ?

Vadim

Fri, 31 Jul 1998 13:46:25 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] OR clause status report

On 31-Jul-98 Bruce Momjian wrote:
> I have succeeded in making OR clauses use indexes.  I have not dealt
> with some of the more complex issues like x=3 or y=5 as Vadim
> mentioned,
> but at least it works.
> 
>       test=> select * from test where x=102532 or x=102533;
>            x
>       ------
>       102532
>       102533
>       (2 rows)
>       
>       test=> explain select * from test where x=102532 or x=102533;
>       NOTICE:  QUERY PLAN:
>       
>       Index Scan using i_test on test  (cost=4.10 size=1 width=4)

Now I've been wondering why my selects are so slow.  Is this telling me
that they're NOT using the index?  And if not, any ideas why?

The select returned 35 rows out of approx 170,000.

-----
campsites=> explain select * from locations where lower(city)='oxford';
NOTICE:  QUERY PLAN:

Seq Scan on locations  (cost=7263.30 size=84899 width=32)

EXPLAIN
campsites=> 
-----

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH   email: vev@michvhf.com   flame-mail: /dev/null
       # include <std/disclaimers.h>                   TEAM-OS2
   Online Searchable Campground Listings    http://www.camping-usa.com
       "There is no outfit less entitled to lecture me about bloat
               than the federal government"  -- Tony Snow
==========================================================================



Fri, 31 Jul 1998 08:54:20 -0400 (EDT)
Vince Vielhaber <vev@michvhf.com>
RE: [HACKERS] OR clause status report
Vince Vielhaber wrote:
> 
> Now I've been wondering why my selects are so slow.  Is this telling me
> that they're NOT using the index?  And if not, any ideas why?
> 
> The select returned 35 rows out of approx 170,000.
> 
> -----
> campsites=> explain select * from locations where lower(city)='oxford';
                                                    ^^^^^^^^^^^
You should 

create index index_name on locations (lower(city))
                                      ^^^^^
- this is known as functional index...

> NOTICE:  QUERY PLAN:
> 
> Seq Scan on locations  (cost=7263.30 size=84899 width=32)

Vadim

Fri, 31 Jul 1998 21:24:50 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] OR clause status report

On 31-Jul-98 Vadim Mikheev wrote:
> Vince Vielhaber wrote:
>> 
>> Now I've been wondering why my selects are so slow.  Is this telling
>> me
>> that they're NOT using the index?  And if not, any ideas why?
>> 
>> The select returned 35 rows out of approx 170,000.
>> 
>> -----
>> campsites=> explain select * from locations where
>> lower(city)='oxford';
>                                                     ^^^^^^^^^^^
> You should 
> 
> create index index_name on locations (lower(city))
>                                       ^^^^^
> - this is known as functional index...
> 

campsites=> create index lower_city on locations (lower(city));
ERROR:  DefineIndex: (null) class not found
campsites=> 

Hmmm..

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH   email: vev@michvhf.com   flame-mail: /dev/null
       # include <std/disclaimers.h>                   TEAM-OS2
   Online Searchable Campground Listings    http://www.camping-usa.com
       "There is no outfit less entitled to lecture me about bloat
               than the federal government"  -- Tony Snow
==========================================================================



Fri, 31 Jul 1998 09:39:51 -0400 (EDT)
Vince Vielhaber <vev@michvhf.com>
Re: [HACKERS] OR clause status report
Vince Vielhaber wrote:
> 
> campsites=> create index lower_city on locations (lower(city));
> ERROR:  DefineIndex: (null) class not found
> campsites=>

Ok, this works:

create index lower_city on locations (lower(city) text_ops);

Something broken in DefineIndex -:((
XXX_ops for function rettype should be used...

Vadim

Fri, 31 Jul 1998 21:46:28 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] OR clause status report

On 31-Jul-98 Vadim Mikheev wrote:
> Vince Vielhaber wrote:
>> 
>> campsites=> create index lower_city on locations (lower(city));
>> ERROR:  DefineIndex: (null) class not found
>> campsites=>
> 
> Ok, this works:
> 
> create index lower_city on locations (lower(city) text_ops);
> 
> Something broken in DefineIndex -:((
> XXX_ops for function rettype should be used...

Yep, it created it that time but still won't use it. :(

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH   email: vev@michvhf.com   flame-mail: /dev/null
       # include <std/disclaimers.h>                   TEAM-OS2
   Online Searchable Campground Listings    http://www.camping-usa.com
       "There is no outfit less entitled to lecture me about bloat
               than the federal government"  -- Tony Snow
==========================================================================



Fri, 31 Jul 1998 10:05:14 -0400 (EDT)
Vince Vielhaber <vev@michvhf.com>
Re: [HACKERS] OR clause status report
> Bruce Momjian wrote:
> > 
> > I have succeeded in making OR clauses use indexes.  I have not dealt
> > with some of the more complex issues like x=3 or y=5 as Vadim mentioned,
> > but at least it works.
> > 
> >         test=> select * from test where x=102532 or x=102533;
> 
> But did you care about the case when two indices (on test(x) and
> on test(y)) exist ?

Haven't gotten to that yet.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Fri, 31 Jul 1998 10:30:47 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] OR clause status report
Vince Vielhaber wrote:
> 
> Yep, it created it that time but still won't use it. :(

Yes, I see.

This is patch for 6.3.2
CVS updated.

Bruce, could you add this to TODO:

>> campsites=> create index lower_city on locations (lower(city));
>> ERROR:  DefineIndex: (null) class not found
>> campsites=>
> 
> Ok, this works:
> 
> create index lower_city on locations (lower(city) text_ops);
> 
> Something broken in DefineIndex -:((
> XXX_ops for function rettype should be used...

Should be easy to fix but no time -:(

Vadim
Fri, 31 Jul 1998 23:04:27 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] OR clause status report
> Vince Vielhaber wrote:
> > 
> > Yep, it created it that time but still won't use it. :(
> 
> Yes, I see.
> 
> This is patch for 6.3.2
> CVS updated.
> 
> Bruce, could you add this to TODO:
> 
> >> campsites=> create index lower_city on locations (lower(city));
> >> ERROR:  DefineIndex: (null) class not found
> >> campsites=>
> > 
> > Ok, this works:
> > 
> > create index lower_city on locations (lower(city) text_ops);
> > 
> > Something broken in DefineIndex -:((
> > XXX_ops for function rettype should be used...
> 
> Should be easy to fix but no time -:(

Added:

* allow creation of functional indexes to use default types

I am a little confused.  You say you updated CVS.  Isn't the change
installed already?

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Fri, 31 Jul 1998 11:15:15 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] OR clause status report
Bruce Momjian wrote:
> 
> Added:
> 
> * allow creation of functional indexes to use default types
> 
> I am a little confused.  You say you updated CVS.  Isn't the change
> installed already?

Functional indices were not used - this is fixed now and
fix is in CVS.

Vadim

Fri, 31 Jul 1998 23:29:20 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] OR clause status report

On 31-Jul-98 Vadim Mikheev wrote:
> Vince Vielhaber wrote:
>> 
>> Yep, it created it that time but still won't use it. :(
> 
> Yes, I see.
> 
> This is patch for 6.3.2
> CVS updated.
> 

campsites=> explain select * from locations where lower(city) = 'oxford';
NOTICE:  QUERY PLAN:

Index Scan using lower_city on locations  (cost=3608.95 size=56600 width=32)

EXPLAIN
campsites=> 


Much better!  Thanks!  Now I get to upgrade the production machine.

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH   email: vev@michvhf.com   flame-mail: /dev/null
       # include <std/disclaimers.h>                   TEAM-OS2
   Online Searchable Campground Listings    http://www.camping-usa.com
       "There is no outfit less entitled to lecture me about bloat
               than the federal government"  -- Tony Snow
==========================================================================



Fri, 31 Jul 1998 12:55:25 -0400 (EDT)
Vince Vielhaber <vev@michvhf.com>
Re: [HACKERS] OR clause status report

On 31-Jul-98 Vince Vielhaber wrote:
> 
> On 31-Jul-98 Vadim Mikheev wrote:
>> Vince Vielhaber wrote:
>>> 
>>> Yep, it created it that time but still won't use it. :(
>> 
>> Yes, I see.
>> 
>> This is patch for 6.3.2
>> CVS updated.
>> 
> 
> campsites=> explain select * from locations where lower(city) = 'oxford';
> NOTICE:  QUERY PLAN:
> 
> Index Scan using lower_city on locations  (cost=3608.95 size=56600
> width=32)
> 
> EXPLAIN
> campsites=> 
> 
> 
> Much better!  Thanks!  Now I get to upgrade the production machine.

Ok, everything's working now but I did run into something unexpected.
The test machine (actually my desktop) has been running the cvsup'd
version since I set ip up a couple of weeks ago.  The production machine
was running 6.3.  The INSTALL file says dumping the db wasn't necessary
yet I had to - it wouldn't run without it and complained about the older
database.  Is dumping going to be required for 6.4?

Also are indexes working for floats when the query uses BETWEEN?

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH   email: vev@michvhf.com   flame-mail: /dev/null
       # include <std/disclaimers.h>                   TEAM-OS2
   Online Searchable Campground Listings    http://www.camping-usa.com
       "There is no outfit less entitled to lecture me about bloat
               than the federal government"  -- Tony Snow
==========================================================================



Fri, 31 Jul 1998 15:12:13 -0400 (EDT)
Vince Vielhaber <vev@michvhf.com>
Re: [HACKERS] OR clause status report - working
> The test machine (actually my desktop) has been running the cvsup'd
> version since I set ip up a couple of weeks ago.  The production 
> machine was running 6.3.  The INSTALL file says dumping the db wasn't 
> necessary yet I had to - it wouldn't run without it and complained 
> about the older database.  Is dumping going to be required for 6.4?

Of course :)

Also, things like the installation docs are the last to be updated
before a release since they need to accurately match the actual release.
Pretty much a waste of time in between...

> Also are indexes working for floats when the query uses BETWEEN?

Probably. The "BETWEEN" becomes an "AND" clause.

Sat, 01 Aug 1998 01:20:15 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] OR clause status report - working

On 01-Aug-98 Thomas G. Lockhart wrote:
>> The test machine (actually my desktop) has been running the cvsup'd
>> version since I set ip up a couple of weeks ago.  The production 
>> machine was running 6.3.  The INSTALL file says dumping the db wasn't 
>> necessary yet I had to - it wouldn't run without it and complained 
>> about the older database.  Is dumping going to be required for 6.4?
> 
> Of course :)
> 
> Also, things like the installation docs are the last to be updated
> before a release since they need to accurately match the actual release.
> Pretty much a waste of time in between...

Ok, that makes real good sense to me.  Just something to learn when using
a cvsup'd version as opposed to a release.

> 
>> Also are indexes working for floats when the query uses BETWEEN?
> 
> Probably. The "BETWEEN" becomes an "AND" clause.

Unfortunately I later found this:

-----
campsites=> explain select name from camps3 where lon = 83.5555;
NOTICE:  QUERY PLAN:

Seq Scan on camps3  (cost=822.87 size=1 width=12)

EXPLAIN
campsites=> 
-----

I have two or three indexes created for lon on camps3.  

create index camps3_lon on camps3 (lon); 
create index camps3_lon2 on camps3 (lon, float4_ops);
create index camps3_loc on camps3 (lon, lat);


Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH   email: vev@michvhf.com   flame-mail: /dev/null
       # include <std/disclaimers.h>                   TEAM-OS2
   Online Searchable Campground Listings    http://www.camping-usa.com
       "There is no outfit less entitled to lecture me about bloat
               than the federal government"  -- Tony Snow
==========================================================================



Fri, 31 Jul 1998 21:21:44 -0400 (EDT)
Vince Vielhaber <vev@michvhf.com>
Re: [HACKERS] OR clause status report - working