Обсуждение: SELECT * FROM t where p or q;

hi,
on a +10 millions rec table, a simple select:

SELECT * FROM tab where num = x;

time: 5 secondes

SELECT * FROM tab where num = y;

time: 5 secondes


SELECT * FROM tab WHERE num = x OR num = y;

time: more than 10 mn !!!

num is an integer.

On Fri, 16 Nov 2001 frbn@efbs-seafrigo.fr wrote:

> hi,
> on a +10 millions rec table, a simple select:
>
> SELECT * FROM tab where num = x;
>
> time: 5 secondes
>
> SELECT * FROM tab where num = y;
>
> time: 5 secondes
>
>
> SELECT * FROM tab WHERE num = x OR num = y;
>
> time: more than 10 mn !!!
>
> num is an integer.

Have you run vacuum analyze, what does explain show
on the three queries?

frbn@efbs-seafrigo.fr writes:
> on a +10 millions rec table, a simple select:
> SELECT * FROM tab where num = x;
> time: 5 secondes

> SELECT * FROM tab WHERE num = x OR num = y;
> time: more than 10 mn !!!

What does EXPLAIN show?  Have you VACUUM ANALYZEd the table recently
(or indeed ever)?  This looks a lot like the behavior on a
never-vacuumed table --- the default statistical assumptions are such
that an indexscan looks like a win for one equality clause, but not
for two ORed clauses.
        regards, tom lane

On Tue, 20 Nov 2001 frbn@efbs-seafrigo.fr wrote:

> thanks to you, after a 15mn long "vacuum ANALYZE table"      :(

Ugh.  7.2s vacuum will be much happier (since it doesn't lock
the tables from other usual use - yay Tom!) but I think there's
also some things you may be able to do to speed it up.  It's
possible dropping indexes and recreating them may be faster.

> I can't figure out how Tera-bytes databases admins can
> deal with such vacuum delays!
> (except if a vacuum is only needed every 10 millions records :] )

It mostly depends on the frequency of deletes and updates to the
database.

frbn@efbs-seafrigo.fr wrote in message news:<3BF51764.8BEAC873@efbs-seafrigo.fr>...

> SELECT * FROM tab WHERE num = x OR num = y;
> 
> time: more than 10 mn !!!
> 
> num is an integer.

Parsers and optimizers don't always see what's _obvious_ to us.

Have you tried:

SELECT * from tab where num in (x,y);

thanks a lot to pay attention to my problem

thanks to you, after a 15mn long "vacuum ANALYZE table"      :(
the "OR" works now just how  it was supposed to.             
I wrongly thought that a "vacuum" did a "vacuum ANALYZE" by default.


I can't figure out how Tera-bytes databases admins can
deal with such vacuum delays!
(except if a vacuum is only needed every 10 millions records :] )

Best regards,
Franck.

Tom Lane wrote :
> 
> frbn@efbs-seafrigo.fr writes:
> > on a +10 millions rec table, a simple select:
> > SELECT * FROM tab where num = x;
> > time: 5 secondes
> 
> > SELECT * FROM tab WHERE num = x OR num = y;
> > time: more than 10 mn !!!
> 
> What does EXPLAIN show?  Have you VACUUM ANALYZEd the table recently
> (or indeed ever)?  This looks a lot like the behavior on a
> never-vacuumed table --- the default statistical assumptions are such
> that an indexscan looks like a win for one equality clause, but not
> for two ORed clauses.
> 
>                         regards, tom lane

> I can't figure out how Tera-bytes databases admins can
> deal with such vacuum delays!
> (except if a vacuum is only needed every 10 millions records :] )
>
> Best regards,
> Franck.

In 7.2, VACUUM will not cause a delay on your database, and ANALYZE can be
run as a separate command.

Chris