Re: [HACKERS] hash partitioning based on v10Beta2

Hi, When the number of partitions and the data are more, adding new partitions, there will be some efficiency problems. I don't know how the solution you're talking about is

Re: [HACKERS] hash partitioning based on v10Beta2

On Mon, Aug 28, 2017 at 10:44 PM, yangjie wrote: > When the number of partitions and the data are more, adding new partitions, > there will be some efficiency problems. > I don't know how the solution you're talking about is how to implement a > hash partition? I am having

Re: [HACKERS] hash partitioning based on v10Beta2

On Sat, Aug 26, 2017 at 12:40 AM, yang...@highgo.com wrote: > A partition table can be create as bellow: > > CREATE TABLE h1 PARTITION OF h; > CREATE TABLE h2 PARTITION OF h; > CREATE TABLE h3 PARTITION OF h; This syntax is very problematic for reasons that have been

Re: [HACKERS] hash partitioning based on v10Beta2

On Sat, Aug 26, 2017 at 10:10 AM, yang...@highgo.com wrote: > Hi all, > > Now we have had the range / list partition, but hash partitioning is not > implemented yet. > Attached is a POC patch based on the v10Beta2 to add the > hash partitioning feature. > Although we will

Re: [HACKERS] hash partitioning based on v10Beta2

On Sat, Aug 26, 2017 at 10:10 AM, yang...@highgo.com wrote: > Hi all, > > Now we have had the range / list partition, but hash partitioning is not > implemented yet. > Attached is a POC patch based on the v10Beta2 to add the > hash partitioning feature. > Although we will

[HACKERS] hash partitioning based on v10Beta2

Hi all, Now we have had the range / list partition, but hash partitioning is not implemented yet. Attached is a POC patch based on the v10Beta2 to add the hash partitioning feature. Although we will need more discussions about the syntax and other specifications before going ahead the

Re: [HACKERS] Hash partitioning.

: [HACKERS] Hash partitioning. On 06/26/2013 05:46 PM, Heikki Linnakangas wrote: We could also allow a large query to search a single table in parallel. A seqscan would be easy to divide into N equally-sized parts that can be scanned in parallel. It's more difficult for index scans, but even

Re: [HACKERS] Hash partitioning.

2013/6/26 Heikki Linnakangas hlinnakan...@vmware.com: On 26.06.2013 16:41, Yuri Levinsky wrote: Heikki, As far as I understand the height of the btree will affect the number of I/Os necessary. The height of the tree does not increase linearly with the number of records. Now let's compare

Re: [HACKERS] Hash partitioning.

2013/6/27 Nicolas Barbier nicolas.barb...@gmail.com: When each index requires one extra I/O (because each index is one level taller), that is 50 extra I/Os. In the partitioned case, each index would require the normal smaller amount of I/Os. [..] Using those other indexes (both for look-ups

Re: [HACKERS] Hash partitioning.

On Jun 27, 2013 12:24 PM, Nicolas Barbier nicolas.barb...@gmail.com wrote: 2013/6/27 Nicolas Barbier nicolas.barb...@gmail.com: When each index requires one extra I/O (because each index is one level taller), that is 50 extra I/Os. In the partitioned case, each index would require the

Re: [HACKERS] Hash partitioning.

On 06/27/2013 11:12 AM, Nicolas Barbier wrote: Imagine that there are a lot of indexes, e.g., 50. Although a lookup (walking one index) is equally fast, an insertion must update al 50 indexes. When each index requires one extra I/O (because each index is one level taller), that is 50 extra

Re: [HACKERS] Hash partitioning.

2013/6/27 Markus Wanner mar...@bluegap.ch: On 06/27/2013 11:12 AM, Nicolas Barbier wrote: Imagine that there are a lot of indexes, e.g., 50. Although a lookup (walking one index) is equally fast, an insertion must update al 50 indexes. When each index requires one extra I/O (because each

Re: [HACKERS] Hash partitioning.

On 06/27/2013 06:35 PM, Nicolas Barbier wrote: I am assuming that this (comparatively very small and super-hot) index is cached all the time, while for the other indexes (that are supposedly super-huge) only the top part stays cached. I am mostly just trying to find out where Yuri’s

Re: [HACKERS] Hash partitioning.

On Wed, Jun 26, 2013 at 8:55 AM, Markus Wanner mar...@bluegap.ch wrote: On 06/26/2013 05:46 PM, Heikki Linnakangas wrote: We could also allow a large query to search a single table in parallel. A seqscan would be easy to divide into N equally-sized parts that can be scanned in parallel.

Re: [HACKERS] Hash partitioning.

On Wed, Jun 26, 2013 at 11:14 AM, Claudio Freire klaussfre...@gmail.comwrote: Now I just have two indices. One that indexes only hot tuples, it's very heavily queried and works blazingly fast, and one that indexes by (hotness, key). I include the hotness value on the query, and still works

Re: [HACKERS] Hash partitioning.

On Thu, Jun 27, 2013 at 2:12 AM, Nicolas Barbier nicolas.barb...@gmail.comwrote: 2013/6/26 Heikki Linnakangas hlinnakan...@vmware.com: On 26.06.2013 16:41, Yuri Levinsky wrote: Heikki, As far as I understand the height of the btree will affect the number of I/Os necessary. The height

Re: [HACKERS] Hash partitioning.

On Thu, Jun 27, 2013 at 9:35 AM, Nicolas Barbier nicolas.barb...@gmail.comwrote: My reasoning was: To determine which index block to update (typically one in both the partitioned and non-partitioned cases), one needs to walk the index first, which supposedly causes one additional (read) I/O

Re: [HACKERS] Hash partitioning.

On 06/27/2013 11:13 PM, Jeff Janes wrote: Wouldn't any IO system being used on a high-end system be fairly good about making this work through interleaved read-ahead algorithms? To some extent, certainly. It cannot possibly get better than a fully sequential load, though. That sounds like it

Re: [HACKERS] Hash partitioning.

On Thu, Jun 27, 2013 at 6:20 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Wed, Jun 26, 2013 at 11:14 AM, Claudio Freire klaussfre...@gmail.com wrote: Now I just have two indices. One that indexes only hot tuples, it's very heavily queried and works blazingly fast, and one that indexes by

Re: [HACKERS] Hash partitioning.

: Tuesday, June 25, 2013 10:33 PM To: Christopher Browne Cc: Yuri Levinsky; Robert Haas; Bruce Momjian; PostgreSQL Mailing Lists Subject: Re: [HACKERS] Hash partitioning. Christopher Browne cbbro...@gmail.com writes: There would indeed be merit in improving the partitioning apparatus, and actually

Re: [HACKERS] Hash partitioning.

On 26.06.2013 11:17, Yuri Levinsky wrote: The main purpose of partitioning in my world is to store billions of rows and be able to search by date, hour or even minute as fast as possible. Hash partitioning sounds like a bad fit for that use case. A regular b-tree, possibly with range

Re: [HACKERS] Hash partitioning.

On Tue, Jun 25, 2013 at 02:52:33PM -0700, Kevin Grittner wrote: Claudio Freire klaussfre...@gmail.com wrote: Did you try select * from foo where (a % 16) = (1::int % 16)? I did.  Using Robert's hashed partitioning table definitions: test=# explain select * from foo where a = 1 and (a %

Re: [HACKERS] Hash partitioning.

: [HACKERS] Hash partitioning. On 26.06.2013 11:17, Yuri Levinsky wrote: The main purpose of partitioning in my world is to store billions of rows and be able to search by date, hour or even minute as fast as possible. Hash partitioning sounds like a bad fit for that use case. A regular b

Re: [HACKERS] Hash partitioning.

On 06/25/2013 11:52 PM, Kevin Grittner wrote: At least until we have parallel query execution. At *that* point this all changes. Can you elaborate on that, please? I currently have a hard time imagining how partitions can help performance in that case, either. At least compared to modern RAID

Re: [HACKERS] Hash partitioning.

On Wed, Jun 26, 2013 at 03:47:43PM +0200, Markus Wanner wrote: On 06/25/2013 11:52 PM, Kevin Grittner wrote: At least until we have parallel query execution. At *that* point this all changes. Can you elaborate on that, please? I currently have a hard time imagining how partitions can

Re: [HACKERS] Hash partitioning.

On 26.06.2013 16:41, Yuri Levinsky wrote: Heikki, As far as I understand the height of the btree will affect the number of I/Os necessary. The height of the tree does not increase linearly with the number of records. The height of a b-tree is O(log n), where n is the number of records.

Re: [HACKERS] Hash partitioning.

; Yuri Levinsky; PostgreSQL-Dev Subject: Re: [HACKERS] Hash partitioning. On Wed, Jun 26, 2013 at 03:47:43PM +0200, Markus Wanner wrote: On 06/25/2013 11:52 PM, Kevin Grittner wrote: At least until we have parallel query execution. At *that* point this all changes. Can you elaborate

Re: [HACKERS] Hash partitioning.

On Wed, Jun 26, 2013 at 05:10:00PM +0300, Heikki Linnakangas wrote: In practice, there might be a lot of quirks and inefficiencies and locking contention etc. involved in various DBMS's, that you might be able to work around with hash partitioning. But from a theoretical point of view, there

Re: [HACKERS] Hash partitioning.

; Bruce Momjian; PostgreSQL Mailing Lists Subject: Re: [HACKERS] Hash partitioning. On 26.06.2013 16:41, Yuri Levinsky wrote: Heikki, As far as I understand the height of the btree will affect the number of I/Os necessary. The height of the tree does not increase linearly with the number

Re: [HACKERS] Hash partitioning.

Heikki Linnakangas hlinnakan...@vmware.com writes: On 26.06.2013 11:17, Yuri Levinsky wrote: When you dealing with company, which has ~350.000.000 users, and you don't want to use key/value data stores: you need hash partitioned tables and hash partitioned table clusters to perform fast

Re: [HACKERS] Hash partitioning.

On 06/26/2013 04:10 PM, Yuri Levinsky wrote: You typically don't want to use b-tree index when yo select more when ~1-2% of your data. Agreed. Indices on columns with very low selectivity don't perform well. (Postgres knows that and uses a sequential scan based on selectivity estimates. Being

Re: [HACKERS] Hash partitioning.

On 06/26/2013 04:01 PM, k...@rice.edu wrote: I think he is referring to the fact that with parallel query execution, multiple partitions can be processed simultaneously instead of serially as they are now with the resulting speed increase. Processing simultaneously is the purpose of parallel

Re: [HACKERS] Hash partitioning.

On Wed, Jun 26, 2013 at 05:04:11PM +0200, Markus Wanner wrote: On 06/26/2013 04:01 PM, k...@rice.edu wrote: I think he is referring to the fact that with parallel query execution, multiple partitions can be processed simultaneously instead of serially as they are now with the resulting

Re: [HACKERS] Hash partitioning.

Markus Wanner mar...@bluegap.ch wrote: On 06/25/2013 11:52 PM, Kevin Grittner wrote: At least until we have parallel query execution.  At *that* point this all changes. Can you elaborate on that, please? I currently have a hard time imagining how partitions can help performance in that case,

Re: [HACKERS] Hash partitioning.

On 26.06.2013 18:34, Kevin Grittner wrote: Markus Wannermar...@bluegap.ch wrote: On 06/25/2013 11:52 PM, Kevin Grittner wrote: At least until we have parallel query execution. At *that* point this all changes. Can you elaborate on that, please? I currently have a hard time imagining how

Re: [HACKERS] Hash partitioning.

On 06/26/2013 05:46 PM, Heikki Linnakangas wrote: We could also allow a large query to search a single table in parallel. A seqscan would be easy to divide into N equally-sized parts that can be scanned in parallel. It's more difficult for index scans, but even then it might be possible at

Re: [HACKERS] Hash partitioning.

On Wed, Jun 26, 2013 at 11:14 AM, Bruce Momjian br...@momjian.us wrote: On Wed, Jun 26, 2013 at 05:10:00PM +0300, Heikki Linnakangas wrote: In practice, there might be a lot of quirks and inefficiencies and locking contention etc. involved in various DBMS's, that you might be able to work

Re: [HACKERS] Hash partitioning.

On Wed, Jun 26, 2013 at 7:01 AM, k...@rice.edu k...@rice.edu wrote: On Wed, Jun 26, 2013 at 03:47:43PM +0200, Markus Wanner wrote: On 06/25/2013 11:52 PM, Kevin Grittner wrote: At least until we have parallel query execution. At *that* point this all changes. Can you elaborate on

[HACKERS] Hash partitioning.

Hi, Do we have any plans to implement Hash Partitioning, maybe I missing this feature? Sincerely yours, Yuri Levinsky, DBA Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222 image002.jpg

Re: [HACKERS] Hash partitioning.

On Tue, Jun 25, 2013 at 03:48:19PM +0300, Yuri Levinsky wrote: Hi, Do we have any plans to implement Hash Partitioning, maybe I missing this feature? You can do it by writing your own constraint and trigger functions that control the hashing. -- Bruce Momjian br...@momjian.us

Re: [HACKERS] Hash partitioning.

: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222 -Original Message- From: Bruce Momjian [mailto:br...@momjian.us] Sent: Tuesday, June 25, 2013 4:21 PM To: Yuri Levinsky Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Hash partitioning. On Tue, Jun 25, 2013 at 03:48

Re: [HACKERS] Hash partitioning.

On Tue, Jun 25, 2013 at 05:19:47PM +0300, Yuri Levinsky wrote: Bruce, Many thanks. According to PostgreSQL documentation it's only range and list partitions are supported. My question is: when I am following your advice, is PostgreSQL will do partitioning pruning on select? My expectation is:

Re: [HACKERS] Hash partitioning.

On Tue, Jun 25, 2013 at 9:21 AM, Bruce Momjian br...@momjian.us wrote: On Tue, Jun 25, 2013 at 03:48:19PM +0300, Yuri Levinsky wrote: Hi, Do we have any plans to implement Hash Partitioning, maybe I missing this feature? You can do it by writing your own constraint and trigger functions

Re: [HACKERS] Hash partitioning.

On Tue, Jun 25, 2013 at 11:02:40AM -0400, Robert Haas wrote: On Tue, Jun 25, 2013 at 9:21 AM, Bruce Momjian br...@momjian.us wrote: On Tue, Jun 25, 2013 at 03:48:19PM +0300, Yuri Levinsky wrote: Hi, Do we have any plans to implement Hash Partitioning, maybe I missing this feature?

Re: [HACKERS] Hash partitioning.

On Tue, Jun 25, 2013 at 11:06 AM, Bruce Momjian br...@momjian.us wrote: Not really. Constraint exclusion won't kick in for a constraint like CHECK (hashme(a) % 16 == 3) and a WHERE clause of the form a = 42. Uh, I thought we checked the constant against every CHECK constraint and only

Re: [HACKERS] Hash partitioning.

On Tue, Jun 25, 2013 at 11:15:24AM -0400, Robert Haas wrote: On Tue, Jun 25, 2013 at 11:06 AM, Bruce Momjian br...@momjian.us wrote: Not really. Constraint exclusion won't kick in for a constraint like CHECK (hashme(a) % 16 == 3) and a WHERE clause of the form a = 42. Uh, I thought we

Re: [HACKERS] Hash partitioning.

On Tue, Jun 25, 2013 at 11:45 AM, Bruce Momjian br...@momjian.us wrote: On Tue, Jun 25, 2013 at 11:15:24AM -0400, Robert Haas wrote: On Tue, Jun 25, 2013 at 11:06 AM, Bruce Momjian br...@momjian.us wrote: Not really. Constraint exclusion won't kick in for a constraint like CHECK (hashme(a)

Re: [HACKERS] Hash partitioning.

Bruce Momjian br...@momjian.us writes: I looked at predtest.c but I can't see how we accept = and = ranges, but not CHECK (a % 16 == 3). It is the '%' operator? I am not sure why the hashme() function is there. Wouldn't it work if hashme() was an immutable function? No. Robert's

Re: [HACKERS] Hash partitioning.

, Israel Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222 -Original Message- From: Robert Haas [mailto:robertmh...@gmail.com] Sent: Tuesday, June 25, 2013 6:55 PM To: Bruce Momjian Cc: Yuri Levinsky; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Hash partitioning

Re: [HACKERS] Hash partitioning.

On Tue, Jun 25, 2013 at 12:08:34PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I looked at predtest.c but I can't see how we accept = and = ranges, but not CHECK (a % 16 == 3). It is the '%' operator? I am not sure why the hashme() function is there. Wouldn't it work if

Re: [HACKERS] Hash partitioning.

Yuri Levinsky escribió: As former Oracle DBA and now simple beginner PostgreSQL DBA I would like to say: the current partitioning mechanism might be improved. Sorry, it seems to me far behind yesterday requirements. I don't think you'll find anybody that disagrees with this. -- Álvaro

Re: [HACKERS] Hash partitioning.

On Tue, Jun 25, 2013 at 12:55 PM, Robert Haas robertmh...@gmail.com wrote: Let me back up a minute. You told the OP that he could make hash partitioning by writing his own constraint and trigger functions. I think that won't work. But I'm happy to be proven wrong. Do you have an example

Re: [HACKERS] Hash partitioning.

On Tue, Jun 25, 2013 at 12:08 PM, Yuri Levinsky yu...@celltick.com wrote: Guys, I am sorry for taking your time. The reason for my question is: As former Oracle DBA and now simple beginner PostgreSQL DBA I would like to say: the current partitioning mechanism might be improved. Sorry, it

Re: [HACKERS] Hash partitioning.

Christopher Browne cbbro...@gmail.com writes: There would indeed be merit in improving the partitioning apparatus, and actually, I think it's been a couple of years since there has been serious discussion of this. We could certainly use a partitioning mechanism that's easier to use than what

Re: [HACKERS] Hash partitioning.

On Tue, Jun 25, 2013 at 4:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: However, I find it hard to think that hash partitioning as such is very high on the to-do list. As was pointed out upthread, the main practical advantage of partitioning is *not* performance of routine queries, but improved

Re: [HACKERS] Hash partitioning.

Claudio Freire klaussfre...@gmail.com wrote: Did you try select * from foo where (a % 16) = (1::int % 16)? I did.  Using Robert's hashed partitioning table definitions: test=# explain select * from foo where a = 1 and (a % 16) = (1 % 16); QUERY PLAN 

Re: [HACKERS] Hash partitioning.

On Tue, Jun 25, 2013 at 6:52 PM, Kevin Grittner kgri...@ymail.com wrote: I agree though, that having an index implementation that can do the first level split faster than any partitioning mechanism can do is better, and that the main benefits of partitioning are in administration, *not*