Subject: Re: Questions on query planner, join types, and work_mem
From: Hannu Krosing <hannu@krosing.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>, Peter Hussey <peter@labkey.com>,
	pgsql-performance <pgsql-performance@postgresql.org>
In-Reply-To: <1280948634.3735.69.camel@hvost>
References: <AANLkTikMwVZKDYLitnsK31R=VNOuTH8NtmDHDGkBR7k8@mail.gmail.com>
	<18551.1280275502@sss.pgh.pa.us>
	<AANLkTimXMR0jMdw+66E4Xx=SqNV37Ne9WfkFjPFZVjbV@mail.gmail.com>
	<25534.1280498632@sss.pgh.pa.us>
	<AANLkTik3oY1=F_uC843c84LxBLNdHxZaWdfjxrAtyBeu@mail.gmail.com>
	<1280819031.14579.38.camel@hvost>
	<AANLkTik6UBt2ezVFzdCM8iaUPkra_7f-Hs_AmGZgvFhh@mail.gmail.com>
	<1280944268.3735.12.camel@hvost>  <22961.1280944836@sss.pgh.pa.us>
	<1280947287.3735.56.camel@hvost>  <1280948634.3735.69.camel@hvost>
Content-Type: text/plain; charset="UTF-8"
Date: Wed, 04 Aug 2010 22:38:42 +0300
Message-ID: <1280950722.3735.89.camel@hvost>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Wed, 2010-08-04 at 22:03 +0300, Hannu Krosing wrote:
> On Wed, 2010-08-04 at 21:41 +0300, Hannu Krosing wrote:
> > On Wed, 2010-08-04 at 14:00 -0400, Tom Lane wrote:
> 
> > > regression=# select name, setting from pg_settings where name like '%cost';
> > >          name         | setting 
> > > ----------------------+---------
> > >  cpu_index_tuple_cost | 0.005
> > >  cpu_operator_cost    | 0.0025
> > >  cpu_tuple_cost       | 0.01
> > >  random_page_cost     | 4
> > >  seq_page_cost        | 1
> > > (5 rows)
> > > 
> > > To model an all-in-RAM database, you can either dial down both
> > > random_page_cost and seq_page_cost to 0.1 or so, or set random_page_cost
> > > to 1 and increase all the CPU costs.  The former is less effort ;-)
> > > 
> > > It should be noted also that there's not all that much evidence backing
> > > up the default values of the cpu_xxx_cost variables.  In the past those
> > > didn't matter much because I/O costs always swamped CPU costs anyway.
> > > But I can foresee us having to twiddle those defaults and maybe refine
> > > the CPU cost model more, as all-in-RAM cases get more common.
> > 
> > Especially the context switch + copy between shared buffers and system
> > disk cache will become noticeable at these speeds.
> > 
> > An easy way to test it is loading a table with a few indexes, once with
> > a shared_buffers value, which is senough for only the main table and
> > once with one that fits both table and indexes,

I re-ran the test, and checked idx_blks_read for 28MB case

hannu=# select * from pg_statio_user_indexes where relname =
'sbuf_test';
| schemaname |  relname  | indexrelname | idx_blks_read | idx_blks_hit 
+------------+-----------+--------------+---------------+--------------
| hannu      | sbuf_test | sbuf_test1   |         71376 |      1620908
| hannu      | sbuf_test | sbuf_test2   |         71300 |      1620365
| hannu      | sbuf_test | sbuf_test3   |         71436 |      1619619


this means that there were a total of 214112 index blocks read back from
disk cache (obviously at least some of these had to be copied the other
way as well).

This seems to indicate about 1 ms for moving pages over user/system
boundary. (Intel Core2 Duo T7500 @ 2.20GHz, Ubuntu 9.10, 4GB RAM)

for 128MB shared buffers the total idx_blks_read for 3 indexes was about
6300 .


> ok, just to back this up I ran the following test with 28MB and 128MB
> shared buffers.
> 
> create table sbuf_test(f1 float, f2 float, f3 float);
> create index sbuf_test1 on sbuf_test(f1);
> create index sbuf_test2 on sbuf_test(f2);
> create index sbuf_test3 on sbuf_test(f3);
> 
> and then did 3 times the following for each shared_buffers setting
> 
> truncate sbuf_test;
> insert into sbuf_test 
> select random(), random(), random() from generate_series(1,600000);
> 
> the main table size was 31MB, indexes were 18MB each for total size of
> 85MB
> 
> in case of 128MB shared buffers, the insert run in 14sec (+/- 1 sec)
> 
> in case of 28MB shared buffers, the insert run between 346 and 431 sec,
> that is 20-30 _times_ slower.
> 
> There was ample space for keeping the indexes in linux cache (it has 1GB
> cached currently) though the system may have decided to start writing it
> to disk, so I suspect that most of the time was spent copying random
> index pages back and forth between shared buffers and disk cache.
> 
> I did not verify this, so there may be some other factors involved, but
> this seems like the most obvious suspect.
> 
> -- 
> Hannu Krosing   http://www.2ndQuadrant.com
> PostgreSQL Scalability and Availability 
>    Services, Consulting and Training
> 
> 
>