RE: Performing partition pruning using row value

Fujii-san

Wednesday, July 8, 2020 3:20 PM, Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com> wrote:
> Seems we can do partition pruning even in Kato-san's case by dong
>
> create type hoge as (c1 int, c2 int);
> create table a( c1 int, c2 int, c3 varchar) partition by range(((c1, c2)::hoge));
> create table a1 partition of a for values from((0, 0)) to ((100, 100)); create table
> a2 partition of a for values from((100, 100)) to ((200, 200)); explain select * from
> a where (c1, c2)::hoge < (99, 99)::hoge;

I hadn't thought of it that way. Thanks.

Regards,
Sho kato
> -----Original Message-----
> From: Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com>
> Sent: Wednesday, July 8, 2020 3:20 PM
> To: Kato, Sho/加藤 翔 <kato-sho(at)fujitsu(dot)com>; 'Amit Langote'
> <amitlangote09(at)gmail(dot)com>
> Cc: Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com>; PostgreSQL-development
> <pgsql-hackers(at)postgresql(dot)org>
> Subject: Re: Performing partition pruning using row value
>
>
>
> On 2020/07/08 13:25, kato-sho(at)fujitsu(dot)com wrote:
> > Amit-san
> >
> > On Wednesday, July 8, 2020 11:53 AM, Amit Langote
> <amitlangote09(at)gmail(dot)com>:
> >> I think the only reason that this is not supported is that I hadn't
> >> tested such a query when developing partition pruning, nor did anyone
> >> else suggest doing so. :)
>
> Seems we can do partition pruning even in Kato-san's case by dong
>
> create type hoge as (c1 int, c2 int);
> create table a( c1 int, c2 int, c3 varchar) partition by range(((c1, c2)::hoge));
> create table a1 partition of a for values from((0, 0)) to ((100, 100)); create table
> a2 partition of a for values from((100, 100)) to ((200, 200)); explain select * from
> a where (c1, c2)::hoge < (99, 99)::hoge;
>
> I'm not sure if this method is officially supported or not, though...
>
> Regards,
>
> --
> Fujii Masao
> Advanced Computing Technology Center
> Research and Development Headquarters
> NTT DATA CORPORATION