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. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
