Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1URlgp-0002xj-0S for pgsql-students@arkaria.postgresql.org; Mon, 15 Apr 2013 15:51:31 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1URlgo-0000R8-GX for pgsql-students@arkaria.postgresql.org; Mon, 15 Apr 2013 15:51:30 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1URlgn-0000Px-93 for pgsql-students@postgresql.org; Mon, 15 Apr 2013 15:51:29 +0000 Received: from mail-ee0-f45.google.com ([74.125.83.45]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1URlgj-0005Nl-DY for pgsql-students@postgresql.org; Mon, 15 Apr 2013 15:51:28 +0000 Received: by mail-ee0-f45.google.com with SMTP id c50so2386698eek.32 for ; Mon, 15 Apr 2013 08:51:24 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=x-received:date:from:to:subject:message-id:in-reply-to:references :x-mailer:mime-version:content-type:content-transfer-encoding; bh=dlioFwS2tziqBEVMQK1QKx3kPuiJJMpQWeGlbIdM4Bs=; b=W2F8HuvGeSzc3s38kGdKN8BOEa32lRjIVSnDP9Aq0uXTQdP/wp63anBJuPmNi+ld3A 1dp/I8kEtJMol7Cnt6WyibMEeUNBs317nKE7AOA70t9en2bKwc0pJ/bI0SXg72k758nZ kHTg6lpgJ4jH3sB5WQFmAvQ5pTdu8vTz31M5X+zH2YvFTHnFmW+udKxZlWgi2J1UOExS Ti39Of6iaRmJNpaJtDYOWsrJhqppMauAoa16GDLdvvCjRHgerSt7z9TFj45gciXMmOx8 kT/Rg5BSNesZkSIWqWE8iNpXnZz+Ew46S1WGY3ksLFnkYQVfXfoZTcFZ38LiNMwMt5uG uidg== X-Received: by 10.15.102.3 with SMTP id bq3mr63503911eeb.42.1366041084282; Mon, 15 Apr 2013 08:51:24 -0700 (PDT) Received: from rozhon.cz (rozhokar1.sh.cvut.cz. [147.32.116.246]) by mx.google.com with ESMTPS id w51sm3661917eev.13.2013.04.15.08.51.23 (version=TLSv1.2 cipher=RC4-SHA bits=128/128); Mon, 15 Apr 2013 08:51:23 -0700 (PDT) Date: Mon, 15 Apr 2013 17:51:22 +0200 From: "Karel K. =?UTF-8?B?Um96aG/FiA==?=" To: pgsql-students@postgresql.org Subject: Re: Google Summer of code 2013 Message-Id: <20130415175122.ca9c29b83ccd4d5f4cd490f6@gmail.com> In-Reply-To: <20130415015400.GR4361@tamriel.snowman.net> References: <20130413183912.e74dfcdc8f0072e2b6c2fb24@gmail.com> <20130414221640.GQ4361@tamriel.snowman.net> <516B59D9.5070106@fuzzy.cz> <20130415015400.GR4361@tamriel.snowman.net> X-Mailer: Sylpheed 3.3.0 (GTK+ 2.24.16; x86_64-redhat-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-students Precedence: bulk Sender: pgsql-students-owner@postgresql.org At first I would like to thank you for your responses and opinions. Of course I don't see all aspects of this problem, so I cannot tell what should be good for future. But I have done some profiles of group by select and I believe, parallel calling of some hash procedures could help. Set an examle: postgres=# \d charlie Table "public.charlie" Column | Type | Modifiers --------+---------+----------- a | integer | not null b | integer | c | integer | Indexes: "charlie_pkey" PRIMARY KEY, btree (a) insert into charlie (a,b,c) values (generate_series(1,15000000), 5, generate_series(1,100000)); postgres=# explain select count(*) from (select min(a), max(b) from charlie group by c offset 0) x; QUERY PLAN -------------------------------------------------------------------------------- ------------ Aggregate (cost=3196549.18..3196549.19 rows=1 width=0) -> Limit (cost=3044301.02..3195298.60 rows=100047 width=12) -> GroupAggregate (cost=3044301.02..3195298.60 rows=100047 width=12) -> Sort (cost=3044301.02..3081800.29 rows=14999711 width=12) Sort Key: charlie.c -> Seq Scan on charlie (cost=0.00..231079.11 rows=1499971 1 width=12) (6 rows) Oprofile output: CPU: Intel Westmere microarchitecture, speed 2.267e+06 MHz (estimated) Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (No unit mask) count 100000 samples % symbol name 85519 38.0101 hash_search_with_hash_value 14009 6.2265 slot_deform_tuple 11808 5.2482 ExecProject 7675 3.4113 slot_getattr 7539 3.3508 advance_aggregates 6932 3.0810 ExecAgg Of course I know, these simply case is only teoretical and in real tables are data much more complicated, but as I can see, almost 40% of CPU time was computed only one hash function: hash_search_with_hash_value. On Sun, 14 Apr 2013 21:54:00 -0400 Stephen Frost wrote: > Tomas, > > * Tomas Vondra (tv@fuzzy.cz) wrote: > > There's certainly a lot of groundwork to do, and I do share the concern > > that the project will have to deal with a lot of dirty work (e.g. when > > transfering data between the processes). But couldn't it be a useful > > part of the discussion? > > A one-off implementation of parallelized hash table building and/or > usage..? No, I don't see that as particularly relevant to the > discussion around how to do parallelize queries. There are a ton of > examples already of parallel hash table building and various other > independent pieces (parallel sort, parallel aggregation, etc). What is > needed for parallel query processing in PG is to figure out what we mean > by it and how to actually implement it. Following that would be making > the query planner and optimizer aware of it, last would be picking a > specific parallelized implementation of each node and writing it (and > that, really, is the 'easy' part in all of this...). > > > I don't expect a commitable patch at the end, but rather something that > > "works" and may be used as a basis for improvements and to build the > > actual groundwork. > > I don't think it'd really help us get any farther with parallel query > execution. To be honest, I'd be a bit surprised if this hasn't been > done already and patches posted to the list in the past.. > > > I think that depends on the workload type. For example for databases > > handling DWH-like queries, parallel hash aggregate is going to be a > > major improvement. > > DWH is what I deal with day-in and day-out and I certainly agree that > parallelizing hash builds would be wonderful- but that doesn't mean that > a patch which implements it without any consideration for the rest of > the challenges around parallel query execution will actually move us, as > a project, any closer to getting it. In fact, I'd expect most DWH > implementations to do what we've done already- massive partitioning and > parallelizing through multiple client connections. > > > Karel mentioned he's currently working on his bachelor thesis, which is > > about hash tables too. That's another reason why he proposed this topic. > > That's wonderful, I'd love to hear about some ways to improve our > hashing system (I've even proposed one modification a few days ago that > I'd like to see tested more). I believe that costing around hashing > needs to be improved too. > > Parallel-anything is a 'sexy' project, but unless it's focused on how we > answer the hard questions around how do we do parallel work efficiently > while maintaining scalability and portability then it's not moving us > forward. > > Thanks, > > Stephen -- Karel K. Rozhoƈ -- Sent via pgsql-students mailing list (pgsql-students@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-students