>> * Allow CLUSTER to sort the table rather than scanning the index
> when it seems likely to be cheaper (Leonardo Francalanci)
>
> Looks like I owe Leonardo Francalanci a pizza.
Well, the patch started from a work by Gregory Stark, and Tom fixed
a nasty bug; but I'll take a slice ;)
On Wed, Oct 5, 2011 at 12:15 PM, Tom Lane wrote:
> Scott Marlowe writes:
>> It used to be that cluster on a very randomly ordered table was much
>> slower than doing something like select * into newtable from oldtable
>> order by col1, col2; Is that still the case in 9.0/9.1?
>
> Fixed in 9.1, p
Thanks Kevin.
Am in 9.1 and tested same scenario, how exactly storage metrics are
calculated. Please comment.
*Table Structure:*
postgres=# \d test
Table "public.test"
Column | Type | Modifiers
+-+---
id | integer |
name | text|
*No. of rows:*
postgr
Scott Marlowe writes:
> It used to be that cluster on a very randomly ordered table was much
> slower than doing something like select * into newtable from oldtable
> order by col1, col2; Is that still the case in 9.0/9.1?
Fixed in 9.1, per release notes:
* Allow CLUSTER to sort the tab
On Wed, Sep 21, 2011 at 11:57 AM, Greg Smith wrote:
> On 09/21/2011 12:13 PM, Venkat Balaji wrote:
>>
>> I as a DBA, suggested to perform VACUUM FULL and RE-INDEXING + ANALYZE to
>> ensure that IO performance and Indexing performance would be good
>
>
> Read http://wiki.postgresql.org/wiki/VACUUM_
Raghavendra wrote:
> Venkat Balaji wrote:
>> [attempt to calculate file space from row layout and number of
>> rows]
> One more point to add to this good discussion, each row header
> will occupy 24 bytes + 4 bytes pointer on page to tuple.
Not to mention:
http://www.postgresql.org/docs/9.
Venkat Balaji writes:
> Hello,
>
> I was attempting to calculate the actual occupied space by a Table.
SELECT relname, reltuples, pg_size_pretty(relpages*8*1024) as size FROM
pg_class, pg_namespace WHERE pg_namespace.oid = pg_class.relnamespace AND
relkind = 'r' AND nspname = 'public' ORDER BY
On Wed, Oct 5, 2011 at 2:38 PM, Venkat Balaji wrote:
> Hello,
>
> I was attempting to calculate the actual occupied space by a Table.
>
> Below is what i did -
>
> I summed up the avg_width of each column of a table from pg_stats, which
> gives me the average size of a row (277 bytes).
>
> select*
Hello,
I was attempting to calculate the actual occupied space by a Table.
Below is what i did -
I summed up the avg_width of each column of a table from pg_stats, which
gives me the average size of a row (277 bytes).
select* sum(avg_width) as average_row_size from pg_stats *where
tablename='ta
Thanks a lot Kevin !
This email has deepened my understanding on the clustering concept.
Keeping this in mind, I have recommended a new disk layout at the OS level
for our production servers so that IOs will be balanced on the disks as
well.
Currently, we do not have mount points divided accordi
Venkat Balaji wrote:
> We CLUSTERED a table using mostly used Index. Application is
> performing better now.
CLUSTER can help in at least four ways:
(1) It eliminates bloat in the table heap.
(2) It eliminates bloat in the indexes.
(3) It can correct fragmentation in the underlying di
Hello,
Thanks for your suggestions !
We CLUSTERED a table using mostly used Index. Application is performing
better now.
Thanks
VB
On Tue, Sep 27, 2011 at 6:01 PM, Venkat Balaji wrote:
> Forgot to mention -
>
> Kevin,
>
> CLUSTER seems to be an very interesting concept to me.
>
> I am thinking
Forgot to mention -
Kevin,
CLUSTER seems to be an very interesting concept to me.
I am thinking to test the CLUSTER TABLE on our production according to the
Index usage on the table.
Will let you know once i get the results.
Regards,
VB
On Tue, Sep 27, 2011 at 5:59 PM, Venkat Balaji wrote:
>
We had performed VACUUM FULL on our production and performance has improved
a lot !
I started using pg_stattuple and pg_freespacemap for tracking freespace in
the tables and Indexes and is helping us a lot.
Thanks for all your inputs and help !
Regards,
VB
On Thu, Sep 22, 2011 at 12:11 AM, Kevi
Venkat Balaji wrote:
> If i got it correct, CLUSTER would do the same what VACUUM FULL
> does (except being fast)
CLUSTER copies the table (in the sequence of the specified index) to
a new set of files, builds fresh indexes, and then replaces the
original set of files with the new ones. So yo
Thanks Greg !
If i got it correct, CLUSTER would do the same what VACUUM FULL does (except
being fast).
CLUSTER is recommended only because it is faster ? As per the link, the
table would be unavailable (for shorter period compared to VACUUM FULL) when
CLUSTER is executed as well. Hope i got it c
On 09/21/2011 12:13 PM, Venkat Balaji wrote:
I as a DBA, suggested to perform VACUUM FULL and RE-INDEXING + ANALYZE
to ensure that IO performance and Indexing performance would be good
Read http://wiki.postgresql.org/wiki/VACUUM_FULL before you run VACUUM
FULL. You probably don't want to do
Thank you very much for your detailed explanation !
I will be working on our existing "auto-vacuuming" strategy to see
if that's optimal. But, we do have VACUUM VERBOSE ANALYZE running at the
cluster level every day and auto-vacuum is aggressive for highly active
tables.
Today, we have vacuumed a
Shaun Thomas wrote:
> Venkat Balaji wrote:
>> I see lot of free spaces or free pages in Tables and Indexes.
>> But, I need to give an exact calculation on how much space will
>> be reclaimed after VACUUM FULL and RE-INDEXING.
>
> Why?
I've been wondering that, too. And talking about the spa
On 09/20/2011 11:22 AM, Venkat Balaji wrote:
Please help me understand how to calculate free space in Tables and
Indexes even after vacuuming and analyzing is performed.
Besides the query Mark gave you using freespacemap, there's also the
pgstattuple contrib module. You'd use it like this:
Can you please help me understand what "blkno" column refers to ?
Thanks
Venkat
On Wed, Sep 21, 2011 at 11:08 AM, Venkat Balaji wrote:
> Thank Everyone for your inputs !
>
> Mark,
>
> We are using 9.0, so, i should be able to make use of this "freespacemap"
> contrib module and would get back to
Thank Everyone for your inputs !
Mark,
We are using 9.0, so, i should be able to make use of this "freespacemap"
contrib module and would get back to you with the results.
I was using below query (which i got it by googling)..
But, was not sure, if its picking up the correct information. I want
On 21/09/11 10:05, Mark Kirkwood wrote:
...then using the freespacemap contrib module should give very
accurate results:
Sorry, should have said - for 8.4 and later!
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://w
On 21/09/11 06:09, Josh Berkus wrote:
Venkat,
I see lot of free spaces or free pages in Tables and Indexes. But, I need to
give an exact calculation on how much space will be reclaimed after VACUUM
FULL and RE-INDEXING.
At present, there is no way to calculate this precisely. You can only
est
Venkat,
> I see lot of free spaces or free pages in Tables and Indexes. But, I need to
> give an exact calculation on how much space will be reclaimed after VACUUM
> FULL and RE-INDEXING.
At present, there is no way to calculate this precisely. You can only
estimate, and estimates have significa
W dniu 2011-09-20 18:22, Venkat Balaji pisze:
> Hello Everyone,
>
> I had posted a query in "GENERAL" category, not sure if that was the
> correct category to post.
>
> Please help me understand how to calculate free space in Tables and
> Indexes even after vacuuming and analyzing is performed.
26 matches
Mail list logo
