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