Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Сравнение СУБД Новый топик    Ответить
 PostgreSQL/Oracle на конкретном запросе. Почему так?  [new]
web_fox
Member

Откуда: Киев
Сообщений: 444
Здравствуйте. Вопрос, наверное, больше к специалистам по PostgreSQL.

Обьясните, пожалуйста, почему нижеследующий запрос выполняется на Oracle 10g 40 сек, а на PostgreSQL 8.3 - 1:30 сек. Железо одинаковое. В чём может быть проблема на PG?

Таблица PG:
CREATE TABLE "public"."space_acc" (
"dat" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
"gbytes" NUMERIC NOT NULL,
CONSTRAINT "space_acc_pkey" PRIMARY KEY("dat")
) WITHOUT OIDS;

Идентичная таблица Oracle:
create table
(
DAT DATE not null,
GBYTES NUMBER not null
);
alter table add primary key (DAT) using index;

Заполнение таблицы Oracle:
INSERT INTO space_acc
    SELECT SYSDATE - round(10000 / 2) + rownum + 0.5 * dbms_random.VALUE() AS dat,
           round(500 + 10 * rownum + 20 * dbms_random.VALUE()) AS GBytes
      FROM (SELECT * FROM dual CONNECT BY 1 = 1)
     WHERE rownum < 10000
Потом экспортим эти же данные в PG.

ЗАПРОС для замера времени (у меня на PG = 1:30 сек):
SELECT space_acc.dat AS d_begin,
       MIN(space_acc1.dat) AS d_end,
       space_acc.gbytes AS s_begin
  FROM space_acc
  JOIN space_acc space_acc1 ON space_acc.dat < space_acc1.dat
 GROUP BY space_acc.dat, space_acc.gbytes;

План Oracle:
SELECT STATEMENT, GOAL = ALL_ROWS		1036	4999000	154969000
 HASH GROUP BY					1036	4999000	154969000
  MERGE JOIN					173	4999000	154969000
   SORT JOIN					78	9999	219978
    TABLE ACCESS FULL	SCOTT	SPACE_ACC	8	9999	219978
   SORT JOIN					10	9999	89991
    INDEX FAST FULL SCAN SCOTT	SYS_C005859	8	9999	89991

План PG:
HashAggregate  (cost=1252858.35..1252983.34 rows=9999 width=24) (actual time=137284.280..137291.990 rows=9998 loops=1)
  ->  Nested Loop  (cost=0.00..1002908.35 rows=33326667 width=24) (actual time=0.068..69450.834 rows=49985001 loops=1)
        ->  Seq Scan on space_acc  (cost=0.00..162.99 rows=9999 width=16) (actual time=0.020..5.259 rows=9999 loops=1)
        ->  Index Scan using space_acc_pkey on space_acc space_acc1  (cost=0.00..58.62 rows=3333 width=8) (actual time=0.014..3.568 rows=4999 loops=9999)
              Index Cond: (space_acc.dat < space_acc1.dat)
Total runtime: 137294.569 ms
Вакуум сделан.
21 апр 09, 22:11    [7094934]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL/Oracle на конкретном запросе. Почему так?  [new]
web_fox
Member

Откуда: Киев
Сообщений: 444
Файл данных для PG.

К сообщению приложен файл (pg_space_acc.rar - 59Kb) cкачать
21 апр 09, 22:18    [7094947]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL/Oracle на конкретном запросе. Почему так?  [new]
web_fox
Member

Откуда: Киев
Сообщений: 444
Файл данных ORA.

К сообщению приложен файл (ora_space_acc.rar - 59Kb) cкачать
21 апр 09, 22:18    [7094948]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL/Oracle на конкретном запросе. Почему так?  [new]
ЯЕХХ
Guest
Оператор "<" в Постгресе не может использоваться для MERGE JOIN.
50 миллионов итераций в NESTED LOOP естественно тормозят.

Скорее всего поможет только переписывание запроса, в данном примере заменить группировку на подзапрос. В Оракле это тоже значительное ускорение даст.
22 апр 09, 05:38    [7095370]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL/Oracle на конкретном запросе. Почему так?  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2892
можно изменить запрос, тогда он будет выполняться на постгресе гораздо быстрее
22 апр 09, 11:28    [7096779]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL/Oracle на конкретном запросе. Почему так?  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 67475
Блог
web_fox
Обьясните, пожалуйста, почему нижеследующий запрос выполняется на Oracle 10g 40 сек, а на PostgreSQL 8.3 - 1:30 сек. Железо одинаковое. В чём может быть проблема на PG?

Основная проблема совершенно точно не в PG. Посмотрите, пожалуйста, внимательно:

Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 
Connected as test

SQL> create table space_acc
  2  (
  3  DAT DATE not null,
  4  GBYTES NUMBER not null
  5  );

Table created

SQL> alter table space_acc add primary key (DAT) using index;

Table altered

SQL> INSERT INTO space_acc
  2      SELECT SYSDATE - round(10000 / 2) + rownum + 0.5 * dbms_random.VALUE() AS dat,
  3             round(500 + 10 * rownum + 20 * dbms_random.VALUE()) AS GBytes
  4        FROM (SELECT * FROM dual CONNECT BY 1 = 1)
  5       WHERE rownum < 10000
  6  ;

9999 rows inserted

SQL> exec dbms_stats.gather_schema_stats (ownname => user);

PL/SQL procedure successfully completed

SQL> set serveroutput on;
SQL> 
SQL> declare
  2    type record_result is record (d_begin date, d_end date, s_begin number);
  3    type record_list is table of record_result;
  4    type record_cur is ref cursor return record_result;
  5    cr record_cur;
  6    r record_list;
  7    t1 timestamp;
  8  begin
  9    -- Мой вариант
 10    t1 := systimestamp;
 11    open cr for select
 12                s.dat d_begin,
 13                lead (s.dat) over (order by s.dat) d_end,
 14                s.gbytes s_begin
 15                from
 16                space_acc s;
 17    fetch cr bulk collect into r;
 18    close cr;
 19    dbms_output.put_line ('Мой вариант: ' || (systimestamp - t1));
 20    -- Ваш вариант
 21    t1 := systimestamp;
 22    open cr for SELECT space_acc.dat AS d_begin,
 23                 MIN(space_acc1.dat) AS d_end,
 24                 space_acc.gbytes AS s_begin
 25                 FROM space_acc
 26                 JOIN space_acc space_acc1 ON space_acc.dat < space_acc1.dat
 27                 GROUP BY space_acc.dat, space_acc.gbytes;
 28    fetch cr bulk collect into r;
 29    close cr;
 30    dbms_output.put_line ('Ваш вариант: ' || (systimestamp - t1));
 31  end;
 32  /

Мой вариант: +000000000 00:00:00.047000000
Ваш вариант: +000000000 00:01:13.313000000

PL/SQL procedure successfully completed

Полагаю, когда лёгким движением руки запрос может быть ускорен с семидесяти секунд до пятидесяти тысячных долей секунды - рассуждать о сравнении серверов немного неуместно. Вообще, фраза "сорок секунд на десяти тысячах записей" должна звучать похоронным колоколом.
22 апр 09, 12:27    [7097448]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL/Oracle на конкретном запросе. Почему так?  [new]
ОКТОГЕН
Member

Откуда:
Сообщений: 2498
softwarer, c PostgreSQL прецеденты были)))))
22 апр 09, 13:03    [7097810]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL/Oracle на конкретном запросе. Почему так?  [new]
ОКТОГЕН
Member

Откуда:
Сообщений: 2498
softwarer, план для вашего варианта в Постгре
запрос (выполнился за 47 милисекунд)
select
 s.dat d_begin,
 lead (s.dat) over (order by s.dat) d_end,
 s.gbytes s_begin
from
space_acc s
план
WindowAgg  (cost=0.00..569.77 rows=9999 width=16)
  ->  Index Scan using space_acc_pkey on space_acc s  (cost=0.00..419.78 rows=9999 width=16)
22 апр 09, 13:13    [7097894]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL/Oracle на конкретном запросе. Почему так?  [new]
ОКТОГЕН
Member

Откуда:
Сообщений: 2498
web_fox, ваш запрос выполнился за 46 секунд

SELECT space_acc.dat AS d_begin,
MIN(space_acc1.dat) AS d_end,
 space_acc.gbytes AS s_begin
  FROM space_acc
   JOIN space_acc space_acc1 ON space_acc.dat < space_acc1.dat
        GROUP BY space_acc.dat, space_acc.gbytes;
план
HashAggregate  (cost=1252858.35..1252983.34 rows=9999 width=24)
  ->  Nested Loop  (cost=0.00..1002908.35 rows=33326667 width=24)
        ->  Seq Scan on space_acc  (cost=0.00..162.99 rows=9999 width=16)
        ->  Index Scan using space_acc_pkey on space_acc space_acc1  (cost=0.00..58.62 rows=3333 width=8)
              Index Cond: (space_acc.dat < space_acc1.dat)
22 апр 09, 13:14    [7097919]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL/Oracle на конкретном запросе. Почему так?  [new]
web_fox
Member

Откуда: Киев
Сообщений: 444
Спасибо за ответы. В данном случае интересует больше не сама возможность ускорить данный запрос, а конкретная причина его медленного выполнения на PG. Как я понял, это из-за
автор
Оператор "<" в Постгресе не может использоваться для MERGE JOIN


Где можно прочитать остальные особенности работы PG именно при выполнении/построении планов запросов? Если в сравнении с Oracle, то вообще идеально. В PG новичёк.
22 апр 09, 16:37    [7099916]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL/Oracle на конкретном запросе. Почему так?  [new]
ОКТОГЕН
Member

Откуда:
Сообщений: 2498
web_fox, а какое у вас железо? Меня просто интересует почему мой результат 47 секунд,
а не 70? Версия PostgreSQL 8.4. Это они так его оптимизировали, или это разница в железе?
22 апр 09, 18:30    [7100865]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL/Oracle на конкретном запросе. Почему так?  [new]
web_fox
Member

Откуда: Киев
Сообщений: 444
ОКТОГЕН, я всё запускал на домашнем компе: двухядерный AMD 4000+, WinXPSP3.
PG 8.3.7 = 90 сек
PG 8.4 = 90 сек
планы одинаковые.
22 апр 09, 21:09    [7101446]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL/Oracle на конкретном запросе. Почему так?  [new]
web_fox
Member

Откуда: Киев
Сообщений: 444
ОКТОГЕН,

обе версии PG во время выполнения использовали одно ядро, т.е. 50%.
22 апр 09, 21:10    [7101449]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL/Oracle на конкретном запросе. Почему так?  [new]
ОКТОГЕН
Member

Откуда:
Сообщений: 2498
web_fox, рискну предположить, что запросы, на которых планировщик особенно "проседает" есть в любой СУБД. Это данность, зависящая от реализации каждой конкретной СУБД. В постгрессовские
исходники не заглядывал.
А может стоит задачка написать особенно тормозной проект?)))
Их есть у меня)) Надо в опциях задать, например, запрет на сканирование индекса.
22 апр 09, 22:13    [7101597]     Ответить | Цитировать Сообщить модератору
Все форумы / Сравнение СУБД Ответить