Re: Inner join question

Hi,
try this on psql console:
explain analyze select tq1.*, tq2.* from
cal_quat_1 tq1, cal_quat_2 tq2
where tq1.timestamp = tq2.timestamp
and tq1.timestamp > '2004-01-12 09:47:56.0000 +0'::timestamp with time zone
and tq1.timestamp < '2004-01-12 09:50:44.7187 +0'::timestamp with time zone
order by tq1.timestamp;
... and examine generated query plan (or post it)
regards, pajout
P.S.
And what about vacuum full analyze ? :)

Randall Skelton wrote:

> Greetings all,
>
> I am trying to do what should be a simple join but the tables are
> 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 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
> (the timestamps are identical in the range of interest).  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 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>