Re: "pivot aggregation" with a patched intarray

> From: Ali Akbar [mailto:the(dot)apaan(at)gmail(dot)com]
> Sent: Freitag, 6. Juni 2014 03:44
> Subject: Re: [HACKERS] "pivot aggregation" with a patched intarray
>
> 2014-06-05 17:18 GMT+07:00 Marc Mamin <M(dot)Mamin(at)intershop(dot)de>:
> > I'm thinking about adding a final function to my aggregate that would
> > replace zero values will nulls, hence transforming the intarray into
> a standard int[], possibly with nullbitmap and a lowerbound that can be
> > 1.
> > This will probably degrade the performance considerably, but may
> reduce the size of the end result for spare data and not too small
> integers...
> > Performances should greatly depend on the data distribution and order
> as they influence the number of palloc.
> > My first tests shown as well better and poorer results.
> >
> > My target is not to get better performances at the first place, but
> to get a pivot structure in an early aggregation stage.
>
> Usually for pivot, i use crosstab function from tablefunc
> (http://www.postgresql.org/docs/9.4/static/tablefunc.html#AEN158550).
> If your patch doesn't perform better, it's more easier to just use
> crosstab. For storing it efficiently, the result can be transformed
> into array manually.

Hello,

crosstab is too restrictive for my use case:
it supports only one row_name column and you have to know the number of returned columns ("categories").
What I'm doing is to combine a count aggregate with a pivot. This is not the same as what crosstab offers.

>
> PS: as Michael Paquier said above, its better if you could send the
> patch in the .patch file format (see:
> https://wiki.postgresql.org/wiki/Working_with_GIT).

I'm first waiting for some positive feedback on the idea itself before to eventually submit this officially.

best regards,

Marc Mamin