pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [INTERFACES] Index unused with OR?
7+ messages / 4 participants
[nested] [flat]
* Re: [INTERFACES] Index unused with OR?
@ 1998-05-14 13:41 Teodorescu Constantin <teo@flex.flex.ro>
parent: Olaf Mittelstaedt <mstaedt@va-sigi.va.fh-ulm.de>
2 siblings, 0 replies; 7+ messages in thread
From: Teodorescu Constantin @ 1998-05-14 13:41 UTC (permalink / raw)
To: pgsql-interfaces; mstaedt@va-sigi.va.fh-ulm.de
The index is used only if scanning the index is faster than scanning the table itself. Trying the index only with a couple of records isn't good.
Try inserting some hundred records inside and VACUUM the database after.
When you vacuum the database you are updating the statistics table used by the query optimizer. The query optimizer will choose to use an index when it found that it worths.
Not using the index in OR queries was some-time ago a bug in PostgreSQL 6.1 if my memory is good. Think that has been solved in new releases, am I wrong ?
Constantin Teodorescu
FLEX Consulting Braila, ROMANIA
^ permalink raw reply [nested|flat] 7+ messages in thread
* Index unused with OR?
@ 1998-05-14 14:20 Olaf Mittelstaedt <mstaedt@va-sigi.va.fh-ulm.de>
0 siblings, 3 replies; 7+ messages in thread
From: Olaf Mittelstaedt @ 1998-05-14 14:20 UTC (permalink / raw)
To: pgsql-interfaces
Hello all,
playing around with the latest ODBC driver I noticed very slow
answers when using OR with an indexed column in the WHERE part
of a SELECT.
To verify this I tried the following directly with psql on the
database server:
postgres=> create database test7;
CREATEDB
postgres=> \c test7
connecting to new database: test7
test7=> create table t7(i1 int4,i2 int4);
CREATE
test7=> create unique index i_t7 on t7(i1);
CREATE
test7=> insert into t7 values(1,2);
INSERT 53741 1
test7=> insert into t7 values(2,3);
INSERT 53742 1
test7=> insert into t7 values(3,4);
INSERT 53743 1
test7=> explain select * from t7 where i1=1;
NOTICE: QUERY PLAN:
Index Scan on t7 (cost=0.00 size=0 width=8)
EXPLAIN
test7=> explain select * from t7 where (i1=1) or (i1=2);
NOTICE: QUERY PLAN:
Seq Scan on t7 (cost=0.00 size=0 width=8)
EXPLAIN
Obviously the index isn't used. Both PostgreSQL 6.3.1 and 6.3.2 are
suffering from this problem.
Any help or hints?
Regards,
Olaf
--
Olaf Mittelstaedt - IuK - mittelstaedt@fh-ulm.de
Fachhochschule Ulm Prittwitzstr. 10 89075 Ulm
Tel.: +49 (0)731-502-8220 Fax: -8270
Nemo me impune lacessit.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] Re: Index unused with OR?
@ 1998-05-14 14:28 Teodorescu Constantin <teo@flex.flex.ro>
parent: Olaf Mittelstaedt <mstaedt@va-sigi.va.fh-ulm.de>
1 sibling, 0 replies; 7+ messages in thread
From: Teodorescu Constantin @ 1998-05-14 14:28 UTC (permalink / raw)
To: mstaedt@va-sigi.va.fh-ulm.de; pgsql-interfaces
> > Not using the index in OR queries was some-time ago a bug in
> > PostgreSQL 6.1 if my memory is good. Think that has been solved in
> > new releases, am I wrong ?
w=> explain select * from p where (m=29000) or (m=30000);
NOTICE: QUERY PLAN:
Seq Scan on p (cost=0.00 size=0 width=10)
It seems that it was not solved :-(
Let's hear also some other answers from the developers ...
Please make another try ! I have discovered once a strange thing : that if I am vacuuming the database as another user than root, the statistics table isn't properly updated. I think that it was a problem with my database, I have reported but noone has reported the same thing again , so ... the problem it has been dropped.
Make another try logging as postgres user, issue "psql yourdb" command and then vacuum. Repeat the EXPLAIN query and check if it's the same result!
Constantin Teodorescu
FLEX Consulting Braila, ROMANIA
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Index unused with OR?
@ 1998-05-14 14:59 Olaf Mittelstaedt <mstaedt@va-sigi.va.fh-ulm.de>
parent: Olaf Mittelstaedt <mstaedt@va-sigi.va.fh-ulm.de>
2 siblings, 2 replies; 7+ messages in thread
From: Olaf Mittelstaedt @ 1998-05-14 14:59 UTC (permalink / raw)
To: pgsql-interfaces
> The index is used only if scanning the index is faster than
> scanning the table itself. Trying the index only with a couple of
> records isn't good.
> Try inserting some hundred records inside and VACUUM the database
> after.
Actually, I discovered the problem using a table containg more than
8000 rows, using the latest release 6.3.2.
> When you vacuum the database you are updating the statistics table
> used by the query optimizer. The query optimizer will choose to
> use an index when it found that it worths.
> Not using the index in OR queries was some-time ago a bug in
> PostgreSQL 6.1 if my memory is good. Think that has been solved in
> new releases, am I wrong ?
This is the real database:
w=> select count(*) from p;
count
-----
8331
(1 row)
w=> vacuum;
VACUUM
w=> explain select * from p where m = 29000;
NOTICE: QUERY PLAN:
Index Scan on p (cost=0.00 size=0 width=10)
EXPLAIN
w=> explain select * from p where (m=29000) or (m=30000);
NOTICE: QUERY PLAN:
Seq Scan on p (cost=0.00 size=0 width=10)
EXPLAIN
Regards,
Olaf
--
Olaf Mittelstaedt - IuK - mittelstaedt@fh-ulm.de
Fachhochschule Ulm Prittwitzstr. 10 89075 Ulm
Tel.: +49 (0)731-502-8220 Fax: -8270
Ash nazg durbatulûk, ash nazg gimbatul,
ash nazg thrakatulûk agh burzum-ishi krimpatul.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] Re: Index unused with OR?
@ 1998-05-14 15:10 Hannu Krosing <hannu@trust.ee>
parent: Olaf Mittelstaedt <mstaedt@va-sigi.va.fh-ulm.de>
1 sibling, 1 reply; 7+ messages in thread
From: Hannu Krosing @ 1998-05-14 15:10 UTC (permalink / raw)
To: Olaf Mittelstaedt <mstaedt@va-sigi.va.fh-ulm.de>; +Cc: pgsql-interfaces
Olaf Mittelstaedt wrote:
This is probably the wrong list, but ...
> Actually, I discovered the problem using a table containg more than
> 8000 rows, using the latest release 6.3.2.
>
> > Not using the index in OR queries was some-time ago a bug in
> > PostgreSQL 6.1 if my memory is good. Think that has been solved in
> > new releases, am I wrong ?
seems _not_ to be fixed :(
> VACUUM
> w=> explain select * from p where m = 29000;
> NOTICE: QUERY PLAN:
>
> Index Scan on p (cost=0.00 size=0 width=10)
>
> EXPLAIN
> w=> explain select * from p where (m=29000) or (m=30000);
> NOTICE: QUERY PLAN:
>
> Seq Scan on p (cost=0.00 size=0 width=10)
>
As a work-around try
select * from p where (m=29000) union select * from p where (m=30000);
it should be much faster.
Btw, EXPLAIN thinks this to cost nothing ;)
Unique (cost=0.00 size=0 width=0)
-> Sort (cost=0.00 size=0 width=0)
-> Append (cost=0.00 size=0 width=0)
Hannu
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] Re: Index unused with OR?
@ 1998-05-14 16:53 Olaf Mittelstaedt <mstaedt@va-sigi.va.fh-ulm.de>
parent: Hannu Krosing <hannu@trust.ee>
0 siblings, 0 replies; 7+ messages in thread
From: Olaf Mittelstaedt @ 1998-05-14 16:53 UTC (permalink / raw)
To: pgsql-interfaces
> This is probably the wrong list, but ...
Sorry for using the wrong list, I discovered the problem using ODBC.
There are now too many PostgreSQL lists to subscribe to all... :(
> select * from p where (m=29000) union select * from p where (m=30000);
Thanks for your hint, the query above works much better.
Regards,
Olaf
--
Olaf Mittelstaedt - IuK - mittelstaedt@fh-ulm.de
Fachhochschule Ulm Prittwitzstr. 10 89075 Ulm
Tel.: +49 (0)731-502-8220 Fax: -8270
Beati pauperes spiritu.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] Re: Index unused with OR?
@ 1998-05-17 11:04 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Olaf Mittelstaedt <mstaedt@va-sigi.va.fh-ulm.de>
2 siblings, 0 replies; 7+ messages in thread
From: Herouth Maoz @ 1998-05-17 11:04 UTC (permalink / raw)
To: Olaf Mittelstaedt <mstaedt@va-sigi.va.fh-ulm.de>; pgsql-interfaces
At 17:59 +0300 on 14/5/98, Olaf Mittelstaedt wrote:
>
> w=> vacuum;
> VACUUM
> w=> explain select * from p where m = 29000;
> NOTICE: QUERY PLAN:
>
> Index Scan on p (cost=0.00 size=0 width=10)
Unrelated to the question in hand - you complained about VACUUM not
properly updating the stats. It seems to me you should use VACUUM ANALYZE
for that to happen. Just vacuuming means elimination of old rows no longer
used.
Herouth
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~1998-05-17 11:04 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-05-14 14:20 Index unused with OR? Olaf Mittelstaedt <mstaedt@va-sigi.va.fh-ulm.de>
1998-05-14 13:41 ` Teodorescu Constantin <teo@flex.flex.ro>
1998-05-14 14:59 ` Olaf Mittelstaedt <mstaedt@va-sigi.va.fh-ulm.de>
1998-05-14 14:28 ` Teodorescu Constantin <teo@flex.flex.ro>
1998-05-14 15:10 ` Hannu Krosing <hannu@trust.ee>
1998-05-14 16:53 ` Olaf Mittelstaedt <mstaedt@va-sigi.va.fh-ulm.de>
1998-05-17 11:04 ` Herouth Maoz <herouth@oumail.openu.ac.il>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox