Many left outer joins with limit performance

Поиск
Список
Период
Сортировка
От Gerhard Wiesinger
Тема Many left outer joins with limit performance
Дата
Msg-id alpine.LFD.2.00.0905011552460.20718@bbs.intern
обсуждение исходный текст
Ответы Re: Many left outer joins with limit performance  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
Hello,

I want to use postgresql for data entries (every minute) from a central heating
system where the timestamp is logged in a table log. For flexibility in the
future for future values and for implementing several high level types I've
modelled the values in a separate key/value table called log_details.

A Query for the last valid entry for today looks like (also defined as a view),
sometimes used without the limit:
SELECT
   l.id AS id,
   l.datetime AS datetime,
   l.tdate AS tdate,
   l.ttime AS ttime,
   d1.value  AS Raumsolltemperatur,
   d2.value  AS Raumtemperatur,
-- a lot more here, stripped for readibility, see link
FROM
   log l
-- Order is relevant here
LEFT OUTER JOIN key_description k1  ON k1.description = 'Raumsolltemperatur'
LEFT OUTER JOIN log_details d1      ON l.id = d1.fk_id AND d1.fk_keyid =
k1.keyid
-- Order is relevant here
LEFT OUTER JOIN key_description k2  ON k2.description = 'Raumtemperatur'
LEFT OUTER JOIN log_details d2      ON l.id = d2.fk_id AND d2.fk_keyid =
k2.keyid
-- a lot more here, stripped for readibility, see link
WHERE
   -- 86400 entries in that timeframe
   datetime    >= '1970-01-01 00:00:00+02'
   AND datetime < '1970-01-02 00:00:00+02'
ORDER BY
   datetime DESC
LIMIT 1;

For me a perfect query plan would look like:
1.) Fetch the one and only id from table log (or fetch even all necessary id
entries when no limit is specifie)
2.) Make the left outer joins

Details (machine details, table definition, query plans, etc.)
can be found to due size limitations at:
http://www.wiesinger.com/tmp/pg_perf.txt

Any ideas how to improve the performance on left outer joins only and how to
improve the planner to get better results?

For this special case a better solution exists but I thing the planner has to
do the work.
-- ...
WHERE
     -- Also slow: id IN
     -- OK: id =
     id = (
       SELECT
         id
       FROM
         log
       WHERE
         datetime    >= '1970-01-01 00:00:00+02'
         AND datetime < '1970-01-02 00:00:00+02'
       ORDER BY
         datetime DESC
       LIMIT 1
     )
ORDER BY
     datetime DESC LIMIT 1;

Any ideas?

Thnx.

Ciao,
Gerhard

--
http://www.wiesinger.com/

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

Предыдущее
От: Grzegorz Jaśkiewicz
Дата:
Сообщение: Re: bad plan and LIMIT
Следующее
От: henk de wit
Дата:
Сообщение: Transparent table partitioning in future version of PG?