Hi, Starting a new thread which continues on from http://www.postgresql.org/message-id/caaphdvoec8ygwoahvsri-84en2k0tnh6gpxp1k59y9juc1w...@mail.gmail.com
To give a brief summary for any new readers: The attached patch allows for INNER JOINed relations to be removed from the plan, providing none of the columns are used for anything, and a foreign key exists which proves that a record must exist in the table being removed which matches the join condition: Example: test=# create table b (id int primary key); CREATE TABLE test=# create table a (id int primary key, b_id int not null references b(id)); CREATE TABLE test=# explain (costs off) select a.* from a inner join b on a.b_id = b.id; QUERY PLAN --------------- Seq Scan on a This has worked for a few years now for LEFT JOINs, so this patch is just extending the joins types that can be removed. This optimisation should prove to be quite useful for views in which a subset of its columns are queried. The attached is an updated patch which fixes a conflict from a recent commit and also fixes a bug where join removals did not properly work for PREPAREd statements. I'm looking for a bit of feedback around the method I'm using to prune the redundant plan nodes out of the plan tree at executor startup. Particularly around not stripping the Sort nodes out from below a merge join, even if the sort order is no longer required due to the merge join node being removed. This potentially could leave the plan suboptimal when compared to a plan that the planner could generate when the removed relation was never asked for in the first place. There are also other cases such as MergeJoins performing btree index scans in order to obtain ordered results for a MergeJoin that would be better executed as a SeqScan when the MergeJoin can be removed. Perhaps some costs could be adjusted at planning time when there's a possibility that joins could be removed at execution time, although I'm not quite sure about this as it risks generating a poor plan in the case when the joins cannot be removed. I currently can't see much of a way around these cases, but in both cases removing the join should prove to be a win, though just perhaps not with the most optimal of plans. There are some more details around the reasons behind doing this weird executor startup plan pruning around here: http://www.postgresql.org/message-id/20141006145957.ga20...@awork2.anarazel.de Comments are most welcome Regards David Rowley
inner_join_removals_2014-11-29_be69869.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
