From: | Dragos Stoichita <ddd(at)genesis(dot)homeip(dot)net> |
---|---|
To: | pgsql-general(at)hub(dot)org |
Subject: | Why is the INTERSECT so slow? |
Date: | 2000-05-16 00:59:26 |
Message-ID: | SAK.2000.05.16.cbenpjjs@ddd |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Lincoln Yeoh | 2000-05-16 01:09:15 | Re: rtree indexes aren't being used with 7.0 |
Previous Message | Joe Karthauser | 2000-05-15 22:04:48 | Migrating from mysql. |