Thread: Re:Am I really stupid???

  I would like to ask again, because I feel really stupid, I get no answer. I always got an answer to my
questions when I mailed in mailing lists until now. What's happening? At least somebody could answer
simply: "Your question is not worth an answer!" but no answer makes me feel really stupid. As I have
spent a lot of time reading documentation and making tests (perhaps not enough) I would really be
pleased if I had an answer to this simple problem (tell me if it is my fault please):

Original message from: Dragos Stoichita
>  Hi, I'm a new user to SQL and PostgreSQL so perhaps my questions below will be a little stupid so
>please excuse me.
>
>  I do this:
>
>  CREATE TABLE t1 ( PRIMARY KEY (f1), f1 INTEGER, f2 INTEGER);
>  CREATE TABLE t2 ( PRIMARY KEY (f1), f1 INTEGER, f2 INTEGER);
>
>  Then I fill each of these tables with say, around 10000 rows.
>
>  When I do:
>
>  SELECT f2 FROM t1 WHERE f1 > 100;
>
>  It is amazingly fast! It takes less than 1 second. And it returns around 3000 rows.
>
>  I do then:
>
>  SELECT f2 FROM t2 WHERE f1 > 100;
>
>  It is also amazingly fast and returns around 4000 rows.
>
>  Then I do:
>
>  SELECT f2 FROM t1 WHERE f1 > 100 INTERSECT SELECT f2 FROM t2 WHERE f1 > 100;
>
>  And it is incredibly *SLOW*!!! I really don't understand, I run postmaster on a 400Mhz pc with 64 megs
>of ram. What's happening? It is only an intersection of integers. If I had to do it in C, I would Quicksort
>the results from the first query, Quicksort the results from the second query, then unique them, then
>intersect them. On a 400 Mhz processor I think it would take less than 1 second. I tested my Quicksort
>routines on a Pentium 120 and remembered it sorted more than 100000 integers per second. And a
>unique algorithm when the elements are ordered is very fast. The same for an intersection algorithm. But
>it takes more than 8 seconds for PostgreSQL to process the INTERSECT.
>
>  Is there an explanation? Is it my fault? Please help me I already switched from another database to this
>one and hoped PostgreSQL would perform well :(
>
>  Dragos Stoichita, 19 year old student in electronics at ESIEE (http://www.esiee.fr)
>
>
>
>

I used to reformat broken email and respond, but lately I don't have
the time for the effort, if you follow the guidlines at
  http://www.lemis.com/email.html

You'll probably have a lot more luck in the future.

As far as INTERSECT being slow, I really don't know, I've found EXCEPT to
be horribly slow in Postgresql, right now we're trying to work out some
funding to get this resolved.

In the meanwhile I think a better way to accomplish your query would
be this:

SELECT
  t1.f2
FROM
  t1, t2
WHERE
  t1.f1 > 100 AND t2.f1 > 100
  AND t1.f2 = t2.f2
;

(I hope) :)

-Alfred

* Dragos Stoichita <ddd@genesis.homeip.net> [000516 15:46] wrote:
>   I would like to ask again, because I feel really stupid, I get no answer. I always got an answer to my
> questions when I mailed in mailing lists until now. What's happening? At least somebody could answer
> simply: "Your question is not worth an answer!" but no answer makes me feel really stupid. As I have
> spent a lot of time reading documentation and making tests (perhaps not enough) I would really be
> pleased if I had an answer to this simple problem (tell me if it is my fault please):
>
> Original message from: Dragos Stoichita
> >  Hi, I'm a new user to SQL and PostgreSQL so perhaps my questions below will be a little stupid so
> >please excuse me.
> >
> >  I do this:
> >
> >  CREATE TABLE t1 ( PRIMARY KEY (f1), f1 INTEGER, f2 INTEGER);
> >  CREATE TABLE t2 ( PRIMARY KEY (f1), f1 INTEGER, f2 INTEGER);
> >
> >  Then I fill each of these tables with say, around 10000 rows.
> >
> >  When I do:
> >
> >  SELECT f2 FROM t1 WHERE f1 > 100;
> >
> >  It is amazingly fast! It takes less than 1 second. And it returns around 3000 rows.
> >
> >  I do then:
> >
> >  SELECT f2 FROM t2 WHERE f1 > 100;
> >
> >  It is also amazingly fast and returns around 4000 rows.
> >
> >  Then I do:
> >
> >  SELECT f2 FROM t1 WHERE f1 > 100 INTERSECT SELECT f2 FROM t2 WHERE f1 > 100;
> >
> >  And it is incredibly *SLOW*!!! I really don't understand, I run postmaster on a 400Mhz pc with 64 megs
> >of ram. What's happening? It is only an intersection of integers. If I had to do it in C, I would Quicksort
> >the results from the first query, Quicksort the results from the second query, then unique them, then
> >intersect them. On a 400 Mhz processor I think it would take less than 1 second. I tested my Quicksort
> >routines on a Pentium 120 and remembered it sorted more than 100000 integers per second. And a
> >unique algorithm when the elements are ordered is very fast. The same for an intersection algorithm. But
> >it takes more than 8 seconds for PostgreSQL to process the INTERSECT.
> >
> >  Is there an explanation? Is it my fault? Please help me I already switched from another database to this
> >one and hoped PostgreSQL would perform well :(
> >
> >  Dragos Stoichita, 19 year old student in electronics at ESIEE (http://www.esiee.fr)
> >
> >
> >
> >

--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."

* Alfred Perlstein (bright@wintelcom.net) [20000516 22:54]:

> In the meanwhile I think a better way to accomplish your query would
> be this:
>
> SELECT
>   t1.f2
> FROM
>   t1, t2
> WHERE
>   t1.f1 > 100 AND t2.f1 > 100
>   AND t1.f2 = t2.f2
> ;
>
> (I hope) :)


You really don't need the t2.f1 > 100 bit because of the latter join.

i.e.,

      SELECT
        t1.f2
      FROM
        t1, t2
      WHERE
        t1.f1 > 100 AND t1.f2 = t2.f2
      ;

should be sufficient.


Hope this helps,

patrick
--
Abstainer: a weak person who yields to the temptation of
denying himself a pleasure.
   -- Ambrose Bierce