[PERFORM] 9.4 performance improvements test

2014-10-16 Thread Josh Berkus
All, Thought I'd share some pgbench runs I did on two servers, one running 9.3 and one running 9.4. A small (512MB) pgbench test didn't show much difference between the two: 9.3.5: transaction type: TPC-B (sort of) scaling factor: 200 query mode: simple number of clients: 16 number of threads:

[PERFORM] CopyManager(In/out) vs. delete/insert directly

2014-10-16 Thread Emi Lu
Hello, Two options for data (>1M), may I know which one better please? (1) copyOut (JDBC copyManager) t1 into a.csv delete t2 where pk.cols in t1 copyIn t2 from a.csv (2) setautoCommit(false); delete t2 where pk.cols in t1; insert t2 select * from t1; Thank you Emi

Re: [PERFORM] Partitioned tables and SELECT ... ORDER BY ... LIMIT

2014-10-16 Thread Дмитрий Шалашов
Hi Jeff, Thanks for clarifications! In my case yes, it's just few blocks, but different ones every time I change user_id value in my WHERE clause. When I change user_id - buffers are no longer "shared hit" in EXPLAIN. This is a bit more worrying. But if there is no easy fix - well, OK. Best re

Re: [PERFORM] Partitioned tables and SELECT ... ORDER BY ... LIMIT

2014-10-16 Thread Felipe Santos
2014-10-16 14:04 GMT-03:00 Jeff Janes : > On Thu, Oct 16, 2014 at 5:35 AM, Дмитрий Шалашов > wrote: > >> Hi, >> >> lets imagine that we have some table, partitioned by timestamp field, and >> we query it with SELECT with ordering by that field (DESC for example), >> with some modest limit. >> Let

Re: [PERFORM] Partitioned tables and SELECT ... ORDER BY ... LIMIT

2014-10-16 Thread Jeff Janes
On Thu, Oct 16, 2014 at 5:35 AM, Дмитрий Шалашов wrote: > Hi, > > lets imagine that we have some table, partitioned by timestamp field, and > we query it with SELECT with ordering by that field (DESC for example), > with some modest limit. > Lets further say that required amount of rows is found

Re: [PERFORM] Partitioned tables and SELECT ... ORDER BY ... LIMIT

2014-10-16 Thread Дмитрий Шалашов
Hi! So this is not obviously normal, I guess?) My version is 9.2.9, constraint_exclusion set to 'partition'. # \d user_feed_master Table "public.user_feed_master" Column |Type | Modifiers +

Re: [PERFORM] Partitioned tables and SELECT ... ORDER BY ... LIMIT

2014-10-16 Thread François Beausoleil
Hello! Le 2014-10-16 à 08:35, Дмитрий Шалашов a écrit : > lets imagine that we have some table, partitioned by timestamp field, and we > query it with SELECT with ordering by that field (DESC for example), with > some modest limit. > Lets further say that required amount of rows is found in the

[PERFORM] Partitioned tables and SELECT ... ORDER BY ... LIMIT

2014-10-16 Thread Дмитрий Шалашов
Hi, lets imagine that we have some table, partitioned by timestamp field, and we query it with SELECT with ordering by that field (DESC for example), with some modest limit. Lets further say that required amount of rows is found in the first table that query encounters (say, latest one). I am just