Join tables using the closest datetime values

Поиск
Список
Период
Сортировка
От Anton Krokovny
Тема Join tables using the closest datetime values
Дата
Msg-id dt10r6$2m5$1@news.hub.org
обсуждение исходный текст
Список pgsql-general
Hello,

I have two tables with same structure:

CREATE TABLE k1 (
    begintime timestamp with time zone NOT NULL,
    rowid serial NOT NULL
);
ALTER TABLE ONLY k1
    ADD CONSTRAINT k1_time_key PRIMARY KEY (begintime);

They contain a huge number of data (about 100000-200000 records).
I need to join these tables on begintime column, but in most cases there are
no equal timestamps in each other. That's why I need to choose the closest
timestamp from another table.

At this moment I'm using the following implementation (sorting and taking
first above):

CREATE OR REPLACE FUNCTION test(x timestamp with time zone, OUT y int) AS
     'SELECT rowid FROM k2 WHERE begintime <= $1 ORDER BY begintime DESC
LIMIT 1'
language SQL;

SELECT rowid, test(begintime) FROM k1;

This algorithm takes about 5 seconds for executing (excluding data
fetching).

Is there any solution faster than the current one?

Anton.



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

Предыдущее
От: "Chad"
Дата:
Сообщение: Re: I see this as the end of BDB in MySQL without a doubt.
Следующее
От: "Bart Golda"
Дата:
Сообщение: Re: Connecting process to PostgreSQL works stunningly slow