Inner join question

Поиск
Список
Период
Сортировка
От Randall Skelton
Тема Inner join question
Дата
Msg-id 7B6BA947-62ED-11D8-B141-000393C92230@brutus.uwaterloo.ca
обсуждение исходный текст
Ответы Re: Inner join question  (Nick Barr <nicky@chuckie.co.uk>)
Re: Inner join question  (Jan Poslusny <pajout@gingerall.cz>)
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: Alex
Дата:
Сообщение: VACUUM Question
Следующее
От: "Ed L."
Дата:
Сообщение: Re: wishlist: dynamic log volume control