Re: PG 9.3 materialized view VS Views, indexes, shared memory

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