pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SELECT * 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