Re: [GENERAL] Inner join question

[Jan Poslusny]

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. +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. +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 [EMAIL PROTECTED]


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [GENERAL] Inner join question

[Nick Barr]

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. +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 [EMAIL PROTECTED]
We need more information to be able to help further. Can you supply:

1. Total number of rows in each table.
2. Results from explain analyze your query
3. key configuration values from postgresql.conf
4. Basic hardware config. (CPU type and number, Total RAM, HDD type, 
size and speed)

But in the mean time can you try the following query instead.

select (tq1.timestamp = tq2.timestamp) as timestamp, tq1.value as q1, 
tq2.value as q2 from cal_quat_1 tq1, cal_quat_2 as tq2 WHERE 
tq1.timestamp=tq2.timestamp AND tq1.timestamp BETWEEN '2004-01-12 
09:47:56. +0'::timestamp AND '2004-01-12 09:50:44.7187 
+0'::timestamp order by tq1.timestamp;

As far as I know, and someone please correct me, this allows the planner 
the most flexibility when figuring out the optimum plan.

Thanks

Nick



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings