Re: [POC] hash partitioning

Hi,

On 09/14/2017 12:05 PM, Robert Haas wrote:
> On Thu, Sep 14, 2017 at 11:39 AM, Jesper Pedersen
> <jesper(dot)pedersen(at)redhat(dot)com> wrote:
>> When I do
>>
>> CREATE TABLE mytab (
>> a integer NOT NULL,
>> b integer NOT NULL,
>> c integer,
>> d integer
>> ) PARTITION BY HASH (b);
>>
>> and create 64 partitions;
>>
>> CREATE TABLE mytab_p00 PARTITION OF mytab FOR VALUES WITH (MODULUS 64,
>> REMAINDER 0);
>> ...
>> CREATE TABLE mytab_p63 PARTITION OF mytab FOR VALUES WITH (MODULUS 64,
>> REMAINDER 63);
>>
>> and associated indexes
>>
>> CREATE INDEX idx_p00 ON mytab_p00 USING btree (b, a);
>> ...
>> CREATE INDEX idx_p63 ON mytab_p63 USING btree (b, a);
>>
>> Populate the database, and do ANALYZE.
>>
>> Given
>>
>> EXPLAIN (ANALYZE, VERBOSE, BUFFERS ON) SELECT a, b, c, d FROM mytab WHERE b
>> = 42
>>
>> gives
>>
>> Append
>> -> Index Scan using idx_p00 (cost rows=7) (actual rows=0)
>> ...
>> -> Index Scan using idx_p63 (cost rows=7) (actual rows=0)
>>
>> E.g. all partitions are being scanned. Of course one partition will contain
>> the rows I'm looking for.
>
> Yeah, we need Amit Langote's work in
> http://postgr.es/m/098b9c71-1915-1a2a-8d52-1a7a50ce79e8@lab.ntt.co.jp
> to land and this patch to be adapted to make use of it. I think
> that's the major thing still standing in the way of this. Concerns
> were also raised about not having a way to see the hash function, but
> we fixed that in 81c5e46c490e2426db243eada186995da5bb0ba7 and
> hopefully this patch has been updated to use a seed (I haven't looked
> yet). And there was a concern about hash functions not being
> portable, but the conclusion of that was basically that most people
> think --load-via-partition-root will be a satisfactory workaround for
> cases where that becomes a problem (cf. commit
> 23d7680d04b958de327be96ffdde8f024140d50e). So this is the major
> remaining issue that I know about.
>

Thanks for the information, Robert !

Best regards,
Jesper