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
how to calculate compression on table in 6.x
Moderator: NorbertKrupa
-
- 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
What errors are you getting from compression.sh?
Re: how to calculate compression on table in 6.x
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:
2. Run the following query to compute the compression ratio:
Patrice
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');
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;