pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
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
Date: Tue, 10 Mar 2015 03:31:29 -0500
Message-ID: <54FEABE1.60402@BlueTreble.com> (raw)
In-Reply-To: <CA+ssMORdU8WZWHzPtEYXrcGzb9fnY3QnV+zoU29-T1yPv=6VmQ@mail.gmail.com>
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>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>

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



view thread (14+ messages)  latest in thread

Message-ID: <54FEABE1.60402@BlueTreble.com>
Permalink:  ../54FEABE1.60402@BlueTreble.com/
Also on:    postgresql.org/message-id/54FEABE1.60402@BlueTreble.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: Jim.Nasby@BlueTreble.com, niparisco@gmail.com
  Subject: Re: PG 9.3 materialized view VS Views, indexes, shared memory
  In-Reply-To: <54FEABE1.60402@BlueTreble.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox