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