Thread: 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