how to calculate compression on table in 6.x

Moderator: NorbertKrupa

Post Reply
potluri27
Newbie
Newbie
Posts: 8
Joined: Thu Sep 13, 2012 5:43 am

how to calculate compression on table in 6.x

Post by potluri27 » Fri Sep 14, 2012 3:27 pm

How do i calulate raw size and compressed achieved in 6.x on a table ?

compression.sh script is broken and doesnot work in 6.x

jpcavanaugh
Intermediate
Intermediate
Posts: 149
Joined: Mon Apr 30, 2012 10:04 pm
Location: New York
Contact:

Re: how to calculate compression on table in 6.x

Post by jpcavanaugh » Fri Sep 14, 2012 7:53 pm

What errors are you getting from compression.sh?

pborne
Newbie
Newbie
Posts: 20
Joined: Mon Feb 18, 2013 1:37 am

Re: how to calculate compression on table in 6.x

Post by pborne » Sun Mar 24, 2013 6:11 pm

It's a two-step process:

1. Ask Vertica to estimate the raw size of the table or schema. Check out the syntax for audit() in the "SQL Reference" document:

Code: Select all

select audit('public', 'table');
2. Run the following query to compute the compression ratio:

Code: Select all

SELECT *, RawSize / CompressedSize AS Ratio
FROM (
    SELECT audit_start_timestamp,
        anchor_table_name,
        size_bytes AS RawSize,
        CAST(SUM(ros_used_bytes) AS DECIMAL(14, 2)) AS CompressedSize
    FROM projection_storage ps
    INNER JOIN user_audits ua ON ps.projection_schema = ua.object_schema
        AND ps.anchor_table_name = ua.object_name
    WHERE audit_start_timestamp > now() - 1
    GROUP BY 1, 2, 3
    ) A
ORDER BY audit_start_timestamp, anchor_table_name;
Patrice

Post Reply

Return to “Vertica Administration Tools”