Thread: OR clause status report
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)
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
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
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)
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
*** src/backend/optimizer/path/indxpath.c.orig Fri Jul 31 22:54:58 1998
--- src/backend/optimizer/path/indxpath.c Fri Jul 31 22:56:38 1998
***************
*** 410,419 ****
int curIndxKey;
Oid curClass;
! if (clauseinfo_list == NIL)
return NIL;
! while (!DoneMatchingIndexKeys(indexkeys, index))
{
List *tempgroup = NIL;
--- 410,419 ----
int curIndxKey;
Oid curClass;
! if (clauseinfo_list == NIL || indexkeys[0] == 0)
return NIL;
! do
{
List *tempgroup = NIL;
***************
*** 443,449 ****
indexkeys++;
classes++;
! }
/* clausegroup holds all matched clauses ordered by indexkeys */
--- 443,449 ----
indexkeys++;
classes++;
! } while (!DoneMatchingIndexKeys(indexkeys, index));
/* clausegroup holds all matched clauses ordered by indexkeys */
***************
*** 474,483 ****
Oid curClass;
bool jfound = false;
! if (join_cinfo_list == NIL)
return NIL;
! while (!DoneMatchingIndexKeys(indexkeys, index))
{
List *tempgroup = NIL;
--- 474,483 ----
Oid curClass;
bool jfound = false;
! if (join_cinfo_list == NIL || indexkeys[0] == 0)
return NIL;
! do
{
List *tempgroup = NIL;
***************
*** 523,529 ****
indexkeys++;
classes++;
! }
/* clausegroup holds all matched clauses ordered by indexkeys */
--- 523,529 ----
indexkeys++;
classes++;
! } while (!DoneMatchingIndexKeys(indexkeys, index));
/* clausegroup holds all matched clauses ordered by indexkeys */
> 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
==========================================================================