Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YTz0h-0006vV-64 for pgsql-students@arkaria.postgresql.org; Fri, 06 Mar 2015 20:38:15 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YTz0f-0007hN-QV for pgsql-students@arkaria.postgresql.org; Fri, 06 Mar 2015 20:38:13 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YTz0e-0007gw-RG for pgsql-students@postgresql.org; Fri, 06 Mar 2015 20:38:12 +0000 Received: from tamriel.snowman.net ([2001:4830:167b::11]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YTz0b-0000l0-1O for pgsql-students@postgresql.org; Fri, 06 Mar 2015 20:38:10 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id 0119A5F79F; Fri, 6 Mar 2015 15:38:07 -0500 (EST) Date: Fri, 6 Mar 2015 15:38:07 -0500 From: Stephen Frost To: Camelia C Cc: pgsql-students@postgresql.org Subject: Re: [GSoC] Optimization tasks Message-ID: <20150306203807.GV29780@tamriel.snowman.net> References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="Auu+kPTPnkAImXQh" Content-Disposition: inline In-Reply-To: X-Editor: Vim http://www.vim.org/ X-Info: http://www.snowman.net X-Operating-System: Linux/3.13.0-45-generic (x86_64) X-Uptime: 15:28:56 up 43 days, 20:18, 12 users, load average: 0.11, 0.17, 0.20 User-Agent: Mutt/1.5.21 (2010-09-15) X-Pg-Spam-Score: -1.9 (-) 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 --Auu+kPTPnkAImXQh Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Hi Camelia! Thanks for your interest in PostgreSQL! * Camelia C (camelia13c@gmail.com) wrote: > My name is Camelia, I carry out Doctoral/PhD research in the field of > database query processing and I'd like to contribute with PostgreSQL > optimization tasks during this year's GSoC: >=20 > - Optimization- add to the join removal code the ability to remove joi= ns > to tables when the join is on a foreign key column (where all entries = must > exist in the parent anyway) There has actually been quite a bit of discussion about exactly that, along with various patches proposed. This work has been lead by David Rowley and the discussion can be found here: http://www.postgresql.org/message-id/flat/CAApHDvocUEYdt1uT+DLDPs2xEu=3Dv3q= JGT6HeXKonQM4rY_OsSA@mail.gmail.com#CAApHDvocUEYdt1uT+DLDPs2xEu=3Dv3qJGT6He= XKonQM4rY_OsSA@mail.gmail.com > - Optimization- Check the set of conditionals on a WHERE clause against > CHECK constraints on the table being queried and remove any conditiona= ls > which *must* be true due to the CHECK constraints This is an interesting optimization that I don't believe anyone is currently working on. We do look at WHERE clauses and compare them against the CHECK constraints for the purposes of working out if the WHERE clause could ever possibly be true (and, if it can't, then we omit that table from consideration for the query), but I don't believe we go the other direction and remove the conditional check when the CHECK shows that the conditional must always be *true*. I'd be very interested in understanding where that particular optimization is relevant (and that will be an important factor which will be considered when deciding if this is a worthwhile effort). > and others that may appear interesting along my review of the source code. I'd certainly be happy to discuss any other thoughts you have along these lines. Note that while we are always interested in improvements to our optimizer, we are also concerned with the length of time that it takes for the optimizer to decide upon a plan. We won't include optimizations which have a high run-time cost unless there is a high probability that they result in a query execution run-time which is much better. > I'm very excited about this opportunity and I hope that the results will = be > as good! It's great to see your interest in the optimizer! > Will the aforementioned subjects be open for work or do other committers > have them under work already? Per above, the first is actively being worked on, but not the second. > Do you have a specific template for the project proposal? I don't believe we do. Hopefully Thom or one of the other admins will correct me if I got that wrong. :) Thanks! Stephen --Auu+kPTPnkAImXQh Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBCgAGBQJU+hAvAAoJEO1sijiDR2RVGZoP/jIaj0jyTtPQVEBX2IiAtXfe Yf8XSx4LccQd0eZmY7SeZNzz/BTBMYn6NMJtz4yQd6V910sJ0jpZpS8dWVC9+10k bQzTjKbYblVocqpkONuAvpLbSqKbE9iPCMYk0/YLQ39p636r/xKe9Cnv9tn3P0G3 2k+U/yTUSdwf1VrXmxPB9WJYGxY/pa1jYrWHuIWB+HGpvbWyQk7k2NmunDpXxoqq 7JJnp8la7AHi64RSNdNUui7xtvCXtLROlq7jgAjJFGEwMUoEceu0LI0vSUPgMJoO VLFj7k/2Jz7JNHOZm3RFswGitaiUe4VUQIrGNZKYMXG+I1Bis8ZDUxQX/d+KRp4M 6uzQuhOIvmPo1gSpefQiLICudZ9svsVGAMefXw3/jxbYzW32F5//NJvjhljPjSol 6kC+AeI3FWJ5WsWreTq/wIDLnyE/CIg+CBddO34DQHbsiOsjtSjiyiLQi3MgmzOJ 3O0iZ7C5sSxKudKo0JgTgc1CUOMfmBhkBoAl44CddacMOpNMIYgXuGz9Bm9S+BoH 0cHKSU290uzcUGN2igZlhkuYBuG4mRBPe57oV3+3ZuhitVsC7JZgzAMGE8wYYT/L 8pTsY0uGnNceSyQdjJZy1zRxGdfbV6PQbMlj4cDKzMIceWAZYeHxTP3U12x63OkT NEGFWTCzaE5qPauMsitn =CEm6 -----END PGP SIGNATURE----- --Auu+kPTPnkAImXQh--