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) > > > >
