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
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 difficulty understandin
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 discussed
on the existing
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 need more discussions ab
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 need more discussions ab
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 project
On Thu, Jun 27, 2013 at 6:20 PM, Jeff Janes wrote:
> On Wed, Jun 26, 2013 at 11:14 AM, Claudio Freire
> 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
>> (hotness, key). I include the ho
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 i
On Thu, Jun 27, 2013 at 9:35 AM, Nicolas Barbier
wrote:
>
> 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 in the non-partitione
On Thu, Jun 27, 2013 at 2:12 AM, Nicolas Barbier
wrote:
> 2013/6/26 Heikki Linnakangas :
>
> > 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 line
On Wed, Jun 26, 2013 at 11:14 AM, Claudio Freire 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
> (hotness, key). I include the hotness value on the query, and still
> works quite fast enough.
On Wed, Jun 26, 2013 at 8:55 AM, Markus Wanner 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. It's more dif
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 “par
2013/6/27 Markus Wanner :
> 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
>> o
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
On Jun 27, 2013 12:24 PM, "Nicolas Barbier"
wrote:
>
> 2013/6/27 Nicolas Barbier :
>
> > 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.
>
> [..]
>
>
2013/6/27 Nicolas Barbier :
> 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 and
> updates) in the
2013/6/26 Heikki Linnakangas :
> 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 that with a
eSQL-Dev
Subject: 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 d
On Wed, Jun 26, 2013 at 7:01 AM, 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 tha
On Wed, Jun 26, 2013 at 11:14 AM, Bruce Momjian 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 around with ha
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
On 26.06.2013 18:34, Kevin Grittner wrote:
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 help
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 help performance in that case,
> either.
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 resultin
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
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
Heikki Linnakangas 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 search and 4-6 table
obert Haas; 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
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, ther
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.
Informa
; Bruce Momjian; 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 c
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
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
ubject: 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 us
On Tue, Jun 25, 2013 at 02:52:33PM -0700, Kevin Grittner wrote:
> Claudio Freire 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
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 partition
t...@sss.pgh.pa.us]
Sent: 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 writes:
> There would indeed be merit in improving the partitioning apparatus,
On Tue, Jun 25, 2013 at 6:52 PM, Kevin Grittner 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* searching.
Indeed,
Claudio Freire 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
---
On Tue, Jun 25, 2013 at 4:32 PM, Tom Lane 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 bulk-data manage
Christopher Browne 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 we have now, whic
On Tue, Jun 25, 2013 at 12:08 PM, Yuri Levinsky 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
> seems to me far b
On Tue, Jun 25, 2013 at 12:55 PM, Robert Haas 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 showing how to do it
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 Her
On Tue, Jun 25, 2013 at 12:08:34PM -0400, Tom Lane wrote:
> Bruce Momjian 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(
46733, 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 partiti
Bruce Momjian 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 description is exact
On Tue, Jun 25, 2013 at 11:45 AM, Bruce Momjian 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 wrote:
>> >> Not really. Constraint exclusion won't kick in for a constraint like
>> >> CHECK (hashme(a) % 16 == 3) and a WHERE
On Tue, Jun 25, 2013 at 11:15:24AM -0400, Robert Haas wrote:
> On Tue, Jun 25, 2013 at 11:06 AM, Bruce Momjian 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
On Tue, Jun 25, 2013 at 11:06 AM, Bruce Momjian 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 scanned partiti
On Tue, Jun 25, 2013 at 11:02:40AM -0400, Robert Haas wrote:
> On Tue, Jun 25, 2013 at 9:21 AM, Bruce Momjian 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?
> >
> >
On Tue, Jun 25, 2013 at 9:21 AM, Bruce Momjian 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 that
> co
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
, Israel
Mobile: +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
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 http://momjian.u
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
<>
57 matches
Mail list logo
