Hi,
On 09/14/2017 12:05 PM, Robert Haas wrote:
On Thu, Sep 14, 2017 at 11:39 AM, Jesper Pedersen
<jesper.peder...@redhat.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-1a7a50ce7...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
