> AFAIU the OP is trying to give the cache a chance of
> doing some useful
> work by partitioning by time so it's going to be forced to
> go to disk
> less.
Exactly
> have you
> considered a couple of
> "levels" to your hierarchy. Maybe bi-hourly (~15
> million records?)
> within the current
On Mon, Jun 14, 2010 at 08:27:49AM -0400, David Wilson wrote:
> On Mon, Jun 14, 2010 at 5:24 AM, Leonardo F wrote:
> > > For "inserts" I do not see the reason why it would be better to
> > > use index partitioning because AFAIK b-tree would behave exactly
> > > the same in both cases.
> >
> > no,
On Mon, Jun 14, 2010 at 8:38 AM, Peter Hunsberger <
peter.hunsber...@gmail.com> wrote:
>
>
> Can you define acceptable? IIRC the OP is looking for 20,000+ inserts /
> sec.
>
>
> He's actually only looking for 2k inserts/sec. With a battery backed
controller I can sustain that, yes. That's also on
On Mon, Jun 14, 2010 at 7:27 AM, David Wilson wrote:
>
>
> On Mon, Jun 14, 2010 at 5:24 AM, Leonardo F wrote:
>>
>> > For "inserts" I do not see the reason
>> > why
>> > it would be better to use index partitioning because AFAIK
>> > b-tree
>> > would behave exactly the same in both cases.
>>
>>
On Mon, Jun 14, 2010 at 5:24 AM, Leonardo F wrote:
> > For "inserts" I do not see the reason
> > why
> > it would be better to use index partitioning because AFAIK
> > b-tree
> > would behave exactly the same in both cases.
>
> no, when the index gets very big inserting random values gets
> very
On 14 June 2010 13:24, Leonardo F wrote:
>> For "inserts" I do not see the reason
>> why
>> it would be better to use index partitioning because AFAIK
>> b-tree
>> would behave exactly the same in both cases.
>
> no, when the index gets very big inserting random values gets
> very slow.
Hm, inter
> For "inserts" I do not see the reason
> why
> it would be better to use index partitioning because AFAIK
> b-tree
> would behave exactly the same in both cases.
no, when the index gets very big inserting random values gets
very slow.
But still, my approach doesn't work because I thought Postg
On 11 June 2010 17:15, Leonardo F wrote:
> Basically what I'm trying to do is to partition the index in the table
> where the data is going to be inserted into smaller indexes, but
> without using partitions: I would use partial indexes.
> "Historic" data will have just the big index...
Well, you
> Well the situation is still ambiguous
> so:
> Is it possible to provide this table and indexes definitions?
> And it
> would be great it you describe the queries you are going to do
> on this table
> or just provide the SQL.
Sure!
Basically what I'm trying to do is to partition the index in
On 11 June 2010 16:29, Leonardo F wrote:
>
>> Could you please explain the reason to do so many
>> partitions?
>
>
> Because otherwise there would be tons of rows in each
> partition, and randomly "updating" the index for that many
> rows 2000 times per second isn't doable (the indexes
> get so bi
> Could you please explain the reason to do so many
> partitions?
Because otherwise there would be tons of rows in each
partition, and randomly "updating" the index for that many
rows 2000 times per second isn't doable (the indexes
get so big that it would be like writing a multi-GB file
random
On 11 June 2010 13:00, Leonardo F wrote:
> a) create 480 partitions, 1 for each hour of the day. 2 indexes on each
> partition
> b) create 20 partitions, and create 24*2 partial indexes on the current
> partition; then the next day (overnight) create 2 global indexes for the
> table and drop the 2
HI all,
I have a very big table (2000 inserts per sec, I have to store 20 days of data).
The table has 2 indexes, in columns that have almost-random values.
Since keeping those two indexes up-to-date can't be done (updating 2000
times per second 2 indexes with random values on such a huge table
Ow Mun Heng <[EMAIL PROTECTED]> writes:
> On Mon, 2008-08-25 at 10:18 -0400, Tom Lane wrote:
>> I suppose code_id is varchar or some such?
> Yep
> After a few more investigation on the usefulness of the partial indexes,
> I found that, it really isn't all that useful, perhaps some experts can
>
On Mon, 2008-08-25 at 10:18 -0400, Tom Lane wrote:
> Ow Mun Heng <[EMAIL PROTECTED]> writes:
> > On Mon, 2008-08-25 at 12:23 +0800, Ow Mun Heng wrote:
> >> CREATE INDEX idx_d_trh_code_id_partial
> >> ON xmms.d_trh_table
> >> USING btree
> >> (code_id) where code_id not in ('P000','000') and code_i
On Wed, 2006-08-16 at 12:15 +0200, MaXX wrote:
> > That should work. Keep in mind that the main idea of an index is to
> > reduce the number of pages that have to be fetched from disk. If the
> > record size is small, you may have at least one ICMP packet on 50% (or
> > more) of the disk pages even
Jeff Davis wrote:
On Tue, 2006-08-15 at 13:13 +0200, MaXX wrote:
[snip]
I have a table in which I store log from my firewall.
For the protocol column (3 distinct values: TCP ~82%, UDP ~17%, ICMP
~1%, the table contains 1.7M rows), I use a partial index to find ICMP
packets faster.
It's ICMP
Gregory Stark wrote:
MaXX <[EMAIL PROTECTED]> writes:
In my understanding, a partial index is only touched when a matching row is
inserted/updated/deleted (index constraint is true), so if I create a partial
index for each protocol, I will slow down my machine as if I had created a
single "norma
Gregory Stark <[EMAIL PROTECTED]> writes:
> But if you're just looking up a single record I wouldn't expect it to be much
> faster to look it up in the smaller partial index than in the larger index.
> Indexes find records in log(n) time and log() grows awfully slowly.
Yeah. Given the proportions
MaXX <[EMAIL PROTECTED]> writes:
> In my understanding, a partial index is only touched when a matching row is
> inserted/updated/deleted (index constraint is true), so if I create a partial
> index for each protocol, I will slow down my machine as if I had created a
> single "normal" index, but
On Tue, 2006-08-15 at 13:13 +0200, MaXX wrote:
> Hi,
>
> I just want to verify if I'm understanding this correctly:
>
> I have a table in which I store log from my firewall.
> For the protocol column (3 distinct values: TCP ~82%, UDP ~17%, ICMP
> ~1%, the table contains 1.7M rows), I use a parti
Hi,
I just want to verify if I'm understanding this correctly:
I have a table in which I store log from my firewall.
For the protocol column (3 distinct values: TCP ~82%, UDP ~17%, ICMP
~1%, the table contains 1.7M rows), I use a partial index to find ICMP
packets faster.
In my understanding
On Wed, Apr 19, 2006 at 03:51:27AM +0200, Florian G. Pflug wrote:
> Tom Lane wrote:
> >elein <[EMAIL PROTECTED]> writes:
> >>I think I want to:
> >>create unique index pk on table tbl (keyp1, keyp2);
> don't you want
> create unique inde pk on table tbl (keyp1, keyp2) where keyp3 is null
> here
Tom Lane wrote:
elein <[EMAIL PROTECTED]> writes:
I think I want to:
create unique index pk on table tbl (keyp1, keyp2);
don't you want
create unique inde pk on table tbl (keyp1, keyp2) where keyp3 is null
here?
create unique index range on table tbl (keyp1, keyp2, keyp3) wher
elein <[EMAIL PROTECTED]> writes:
> I think I want to:
> create unique index pk on table tbl (keyp1, keyp2);
> create unique index range on table tbl (keyp1, keyp2, keyp3) where
> keyp3 is not null;
> Are these indexes redundant given uniqueness requirement and the type of
> queries
In 8.1.2.
Table contains columns:
keyp1 not null, keyp2 not null, keyp3 nullable.
The queries will be separated into two kinds:
one on those rows where keyp3 is null and
the second on where keyp3 is not null.
I think I want to:
create unique index pk on table tbl (keyp1, keyp2);
Unregistered <[EMAIL PROTECTED]> writes:
> So what I did was:
> made an "today_idx", "2_weeks_idx", "1 month_idx" on the attribute
> "sent_date" to speed up the queries using the "sent_date"
> attribute,which 90% of the times is queried on. These indexes are
> rebuild every night.
> When I was t
I have following situation:
one "message" table and 3 other "message_{1,2,3}" tables which inherit
from "message".
Every day +-50 000 "messages" are being inserted.
I needed something extra to speed up the queries instead of only using
indexes, so I thought of doing something like you can do in
28 matches
Mail list logo
