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

Vadim wrote:

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 ?

Do you mean using two indices in one access plan, or the decision which index to use ?

I think the case Bruce has fixed was the most important for those "alien MS Access users" among us.
Thanks Bruce !

Andreas

Andreas Zeugswetter wrote:

Vadim wrote:

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 ?

Do you mean using two indices in one access plan, or the decision which index to use ?

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
This.

Vadim

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
==========================================================================

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

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
==========================================================================

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

Vadim Mikheev wrote:

Andreas Zeugswetter wrote:

Vadim wrote:

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 ?

Do you mean using two indices in one access plan,

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
This.

Actually, I meant NOT USING indices if both exist, because of
currently there is no check that tuples returned by second index
were not returned by first.

or the decision which index to use ?

Either both indices should be used or no one...

Also, Bruce, did you test the case (x = 5 or x > 4) ?
What about (x = 5 or x = 5)? - I'm not sure does cnfify()
get rid of duplicates or not...

Vadim

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
==========================================================================

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)

Andreas Zeugswetter wrote:

Vadim wrote:

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 ?

Do you mean using two indices in one access plan, or the decision which index to use ?

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
This.

Yes. I still need to run some tests. Just wanted people to know I had
gotten the trivial case working so far.

-- 
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)

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

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)

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

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
==========================================================================

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
==========================================================================

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.

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
==========================================================================

Vadim Mikheev wrote:

Andreas Zeugswetter wrote:

Vadim wrote:

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 ?

Do you mean using two indices in one access plan,

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
This.

Actually, I meant NOT USING indices if both exist, because of
currently there is no check that tuples returned by second index
were not returned by first.

or the decision which index to use ?

Either both indices should be used or no one...

Also, Bruce, did you test the case (x = 5 or x > 4) ?
What about (x = 5 or x = 5)? - I'm not sure does cnfify()
get rid of duplicates or not...

It does get rid of duplicates, and only uses indexes if ALL clauses have
an availble index, but as you noted, x=5 or x > 4 must be handled. It
works now:

test=> select * from test where x >= 102665 or x= 102665;
x
------
102665
(1 row)

test=> explain select * from test where x >= 102665 or x= 102665;
NOTICE: QUERY PLAN:

Index Scan using i_test on test (cost=1503.32 size=1 width=4)

I do it with this code:

ExecStoreTuple(tuple, /* tuple to store */
slot, /* slot to store in */
buffer, /* buffer associated with tuple */
false); /* don't pfree */

for (prev_index = 0; prev_index < indexstate->iss_IndexPtr;
prev_index++)
{
if (ExecQual(nth(prev_index, node->indxqual),
scanstate->cstate.cs_ExprContext))
{
prev_matches = true;
break;
}
}
if (!prev_matches)
return slot;

On an index scan, I compare the qualifications of previous OR index
scans, and return the row only if the current row does not match one of
the previous qualifications. Sounds like a winner. I have not yet
committed this code to the CVS tree.

-- 
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)