Message-ID: <54FEABE1.60402@BlueTreble.com>
Date: Tue, 10 Mar 2015 03:31:29 -0500
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9;
 rv:31.0) Gecko/20100101 Thunderbird/31.4.0
MIME-Version: 1.0
To: Nicolas Paris <niparisco@gmail.com>
CC: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: PG 9.3 materialized view VS Views, indexes, shared
 memory
References: 
 <CA+ssMOQSXAa5uOLZSeanUNtC1YuSVZfoovX73PxU_MQO4o59Hg@mail.gmail.com>
	<CAJghg4+dS5+BDtw6PWGj72hB8YpivMiBGmynEof012pY4yfjQg@mail.gmail.com>
	<CA+ssMOSXAxawWJTO60Vv3ecA1QYy0O8U139kuxLWv3bD11pLug@mail.gmail.com>
	<CAJghg4KV6=bkFEbTS9McG0MPWFLpc_YX1n36iy32o9WPvHNL+Q@mail.gmail.com>
	<CA+ssMOQS_PCL+K9VAYa9RSoTYRfR1R==dEmx9YBnu00Ws_nQug@mail.gmail.com>
	<CA+ssMOS-LJ9woAtSj4xTK2gMXG8RvkmyE7DWfeP1k07TdKF8Qg@mail.gmail.com>
	<54F905A3.7090602@BlueTreble.com>
	<CA+ssMOTCDWk_1BbeaqcepfsRa_KhkSx979nfUxjK3Eu3GZzzew@mail.gmail.com>
	<54F97414.9040208@BlueTreble.com>
	<CA+ssMOTh6H46KaEBgHKJ1F6Aq0+QV_XEOXqSeZ9cDiPXbpKH7w@mail.gmail.com>
	<CA+ssMOT54r9Q349ZBRhuVg6n-3-cp0TpG8Piy5qCTLfumO_Yog@mail.gmail.com>
 <CA+ssMORdU8WZWHzPtEYXrcGzb9fnY3QnV+zoU29-T1yPv=6VmQ@mail.gmail.com>
In-Reply-To: 
 <CA+ssMORdU8WZWHzPtEYXrcGzb9fnY3QnV+zoU29-T1yPv=6VmQ@mail.gmail.com>
Content-Type: text/plain; charset="utf-8"; format=flowed
Content-Transfer-Encoding: 7bit
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 10 Mar 2015 08:31:36.3939 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BN3PR11MB0340
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance