Skip site navigation (1) Skip section navigation (2)

Re: Postgresql 'eats' all mi data partition

From: Gaetano Mendola <mendola(at)bigfoot(dot)com>
To: "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org>
Cc: Tomas Szepe <szepe(at)pinerecords(dot)com>
Subject: Re: Postgresql 'eats' all mi data partition
Date: 2003-09-27 11:20:25
Message-ID: 3F757279.90403@bigfoot.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Tomas Szepe wrote:

>>[mendola(at)bigfoot(dot)com]
>>
>>Tomas Szepe wrote:
>>
>>>>[tgl(at)sss(dot)pgh(dot)pa(dot)us]
>>>>
>>>>
>>>>
>>>>>indexes:
>>>>>stats_min_pkey primary key btree (ip, "start")
>>>>>stats_min_start btree ("start")
>>>>>stats_hr_pkey primary key btree (ip, "start")
>>>>>stats_hr_start btree ("start")
>>>>
>>>>>ip is of type "inet" in all tables.
>>>>>start is of type "timestamp without time zone" in all tables.
>>>>
>>>>Okay, so a pkey index entry will take 32 bytes counting overhead ...
>>>>you've got about 10:1 bloat on the stats_min indexes and 2:1 in stats_hr.
>>>>Definitely bad :-(
>>>
>>>
>>>The only difference between the way stats_min and stats_hr are updated
>>>stems from the fact that stats_min only holds records for the last 1440
>>>minutes (because of its killer time granularity), whereas stats_hr
>>>holds its data until we decide some of it is obsolete enough and
>>>issue a "delete from" by hand.
>>
>>Are you sure that all indexes are needed and that a partial index could 
>>not help ? What about the statistics on these indexes ? Are they really 
>>used ?
> 
> 
> Yup, they're all essential. :(

May I see yours tipical queries where these indexes are involved ?


Regards
Gaetano Mendola



In response to

Responses

pgsql-bugs by date

Next:From: Tomas SzepeDate: 2003-09-27 11:31:27
Subject: Re: Postgresql 'eats' all mi data partition
Previous:From: Tomas SzepeDate: 2003-09-27 10:57:40
Subject: Re: Postgresql 'eats' all mi data partition

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group