Re: Manipulating complex types as non-contiguous structures in-memory

Поиск
Список
Период
Сортировка
От Pavel Stehule
Тема Re: Manipulating complex types as non-contiguous structures in-memory
Дата
Msg-id CAFj8pRAuLQTyARQooSL2wP=x+zp0Y2ZiNAZh1NVV2ZM-Xg282A@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Manipulating complex types as non-contiguous structures in-memory  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: Manipulating complex types as non-contiguous structures in-memory  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-hackers
Дерево обсуждения
Hi

I did some test with unlogged table in shared buffers

foo(a int[]);  -- 2K long array 100K rows

for queries

select max(v) from (unnest(a) from foo) x;
select max(a[1]) from foo
select max(a[2000]) from foo

I didn't find significant slowdown.

Some slowdown is visible (about 10%) for query

update foo set a = a || 1;

Significant slowdown is on following test:

do $$ declare a int[] := '{}'; begin for i in 1..90000 loop a := a || 10; end loop; end$$ language plpgsql;
do $$ declare a numeric[] := '{}'; begin for i in 1..90000 loop a := a || 10.1; end loop; end$$ language plpgsql;

integer master 14sec x patched 55sec
numeric master 43sec x patched 108sec

It is probably worst case - and it is known plpgsql antipattern

Regards

Pavel



2015-05-01 21:59 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> Test for 3000 elements:

>                    Original     Patch
> Integer            55sec      8sec
> Numeric           341sec      8sec

> Quicksort is about 3x faster -- so a benefit of this patch is clear.

Yeah, the patch should pretty much blow the doors off any case that's
heavily dependent on access or update of individual array elements ...
especially for arrays with variable-length element type, such as numeric.

What I'm concerned about is that it could make things *slower* for
scenarios where that isn't the main thing being done with the arrays,
as a result of useless conversions between "flat" and "expanded"
array formats.  So what we need is to try to benchmark some cases
that don't involve single-element operations but rather whole-array
operations (on arrays that are plpgsql variables), and see if those
cases have gotten noticeably worse.

                        regards, tom lane

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

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: [COMMITTERS] pgsql: Add transforms feature
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Manipulating complex types as non-contiguous structures in-memory