pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT * FROM t where p or q;
7+ messages / 5 participants
[nested] [flat]
* SELECT * FROM t where p or q;
@ 2001-11-16 13:40 frbn@efbs-seafrigo.fr
2001-11-20 08:10 ` Re: SELECT * FROM t where p or q; Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-11-20 15:40 ` Re: SELECT * FROM t where p or q; Tom Lane <tgl@sss.pgh.pa.us>
2001-11-21 00:15 ` Re: SELECT * FROM t where p or q; Calvin Dodge <caldodge@fpcc.net>
0 siblings, 3 replies; 7+ messages in thread
From: frbn@efbs-seafrigo.fr @ 2001-11-16 13:40 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT * FROM t where p or q;
2001-11-16 13:40 SELECT * FROM t where p or q; frbn@efbs-seafrigo.fr
@ 2001-11-20 08:10 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2 siblings, 0 replies; 7+ messages in thread
From: Stephan Szabo @ 2001-11-20 08:10 UTC (permalink / raw)
To: frbn@efbs-seafrigo.fr; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT * FROM t where p or q;
2001-11-16 13:40 SELECT * FROM t where p or q; frbn@efbs-seafrigo.fr
@ 2001-11-20 15:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-11-20 17:42 ` Re: SELECT * FROM t where p or q; frbn@efbs-seafrigo.fr
2 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2001-11-20 15:40 UTC (permalink / raw)
To: frbn@efbs-seafrigo.fr; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT * FROM t where p or q;
2001-11-16 13:40 SELECT * FROM t where p or q; frbn@efbs-seafrigo.fr
2001-11-20 15:40 ` Re: SELECT * FROM t where p or q; Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-11-20 17:42 ` frbn@efbs-seafrigo.fr
2001-11-20 17:58 ` Re: SELECT * FROM t where p or q; Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-11-23 01:48 ` Re: SELECT * FROM t where p or q; Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 2 replies; 7+ messages in thread
From: frbn@efbs-seafrigo.fr @ 2001-11-20 17:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; sszabo@megazone23.bigpanda.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT * FROM t where p or q;
2001-11-16 13:40 SELECT * FROM t where p or q; frbn@efbs-seafrigo.fr
2001-11-20 15:40 ` Re: SELECT * FROM t where p or q; Tom Lane <tgl@sss.pgh.pa.us>
2001-11-20 17:42 ` Re: SELECT * FROM t where p or q; frbn@efbs-seafrigo.fr
@ 2001-11-20 17:58 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 0 replies; 7+ messages in thread
From: Stephan Szabo @ 2001-11-20 17:58 UTC (permalink / raw)
To: frbn@efbs-seafrigo.fr; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT * FROM t where p or q;
2001-11-16 13:40 SELECT * FROM t where p or q; frbn@efbs-seafrigo.fr
2001-11-20 15:40 ` Re: SELECT * FROM t where p or q; Tom Lane <tgl@sss.pgh.pa.us>
2001-11-20 17:42 ` Re: SELECT * FROM t where p or q; frbn@efbs-seafrigo.fr
@ 2001-11-23 01:48 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
1 sibling, 0 replies; 7+ messages in thread
From: Christopher Kings-Lynne @ 2001-11-23 01:48 UTC (permalink / raw)
To: frbn@efbs-seafrigo.fr; Tom Lane <tgl@sss.pgh.pa.us>; sszabo@megazone23.bigpanda.com; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT * FROM t where p or q;
2001-11-16 13:40 SELECT * FROM t where p or q; frbn@efbs-seafrigo.fr
@ 2001-11-21 00:15 ` Calvin Dodge <caldodge@fpcc.net>
2 siblings, 0 replies; 7+ messages in thread
From: caldodge@fpcc.net @ 2001-11-21 00:15 UTC (permalink / raw)
To: pgsql-sql
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);
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2001-11-23 01:48 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-16 13:40 SELECT * FROM t where p or q; frbn@efbs-seafrigo.fr
2001-11-20 08:10 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-11-20 15:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-11-20 17:42 ` frbn@efbs-seafrigo.fr
2001-11-20 17:58 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-11-23 01:48 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2001-11-21 00:15 ` Calvin Dodge <caldodge@fpcc.net>
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