On 3/9/15 8:17 AM, Nicolas Paris wrote:
(sorry for top-posting, gmail does not help.)
*shakes fist at gmail*
Thanks to your advice Jim, I have done an other test :
No partial indexes, just a partial index on boolean columns does the
job. (I get same perfs as MV)
CREATE INDEX ..ON (BoolColumnX) WHERE BoolColumnX IS TRUE
Then VIEW =
SELECT colA....colZ
FROM huge_table
WHERE BoolColumnX IS TRUE
Then this only index is used 800times (for each bool col) and saves
place as it does'nt indexes NULL values, and does no replicate. subsets.
Moreover the huge indexes are allways loaded in cache memory.
Cool. :)
According to this link
http://postgresql.nabble.com/NULL-saves-disk-space-td4344106.html
NULL values do not take place if only one other column are null for
that row.
Boolean takes 1 byte wheras smallint 2bytes.
Then the space problem is not anymore a problem with boolean columns
95% empty
One thing that is really great with postgresql is transaction for
drop table cascade, that allow te restore all stuf index, views on a
rollback if problem in loading appears.
I hope using one transaction to drop/load many table is not a
performance issue ?
Why are you dropping and re-loading? You mentioned it before and it
sounded like it had something to do with adding columns, but you don't
have to drop and reload to add a column.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
