Join with equals?

[Randall Skelton]

Greetings all,

I am trying to do what should be a simple join but the tables are very
large and it is taking a long, long time. I have the feeling that I
have stuffed up something in the syntax.

Here is what I have:

telemetry=> select (tq1.timestamp = tq2.timestamp) as timestamp,
tq1.value as q1, tq2.value as q2 from cal_quat_1 tq1 inner join
cal_quat_2 as tq2 using (timestamp) where timestamp > '2004-01-12
09:47:56.0000 +0' and timestamp < '2004-01-12 09:50:44.7187 +0' order
by timestamp;

telemetry=> \d cal_quat_1
Table "cal_quat_1"
Column | Type | Modifiers
-----------+--------------------------+-----------
timestamp | timestamp with time zone |
value | double precision |

telemetry=> \d cal_quat_2
Table "cal_quat_2"
Column | Type | Modifiers
-----------+--------------------------+-----------
timestamp | timestamp with time zone |
value | double precision |

My understanding of an inner join is that the query above will restrict
this to finding tq1.timestamp, tq1.value and then move onto t12.value
to search the subset. I have tried this with and without the '=' sign
and it isn't clear if it is making any difference at all. I have not
allowed the query to finish as it seems to take more than 10 minutes.
Both timestamps are indexed and I expect about 150 rows to be returned.
At the end of the day, I have four identical tables of quaternions
(timestamp, value) and I need to extract them all for a range of
timestamps.

Cheers,
Randall
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Martijn van Oosterhout]

On Wed, Feb 18, 2004 at 11:58:24AM -0500, Randall Skelton wrote:

Greetings all,

I am trying to do what should be a simple join but the tables are very
large and it is taking a long, long time. I have the feeling that I
have stuffed up something in the syntax.

Here is what I have:

telemetry=> select (tq1.timestamp = tq2.timestamp) as timestamp,
tq1.value as q1, tq2.value as q2 from cal_quat_1 tq1 inner join
cal_quat_2 as tq2 using (timestamp) where timestamp > '2004-01-12
09:47:56.0000 +0' and timestamp < '2004-01-12 09:50:44.7187 +0' order
by timestamp;
Please run ANALYZE and then send the EXPLAIN ANALYZE <query> output.
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ If the Catholic church can survive the printing press, science fiction
will certainly weather the advent of bookwarez.
http://craphound.com/ebooksneitherenorbooks.txt - Cory Doctorow

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFANpCVY5Twig3Ge+YRAmmtAJ0ZbL9n8WzSDwKyTBsEFy dGxMi4AwCfetzv
z1EzgFs/NuFcH16sxqA+//E=
=Wp1O
-----END PGP SIGNATURE-----