|From:||Alexander Kuzmenkov <a(dot)kuzmenkov(at)postgrespro(dot)ru>|
|To:||David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>|
|Subject:||Re: Removing unneeded self joins|
|Views:||Raw Message | Whole Thread | Download mbox|
I tried to implement your suggestions, here are the patches.
The first patch mostly moves some code around without changing
functionality. It modifies innerrel_is_unique to not only cache the fact
that the relation is unique, but also
cache the index that guarantees uniqueness.
The second patch adds the unique self join removal code. It goes along
the lines of your plan. I didn't even have to examine join clauses
because the constraints imposed by innerrel_is_unique are strong enough
to guarantee that when it finds the same unique index for both
relations, the join can be removed. Namely, it requires mergejoinable
equality clauses for all columns of a unique index.
As a simple benchmark, I measured the duration of query_planner and
remove_useless_self_joins with clock_gettime() on the regression tests.
The following table shows average times in microseconds, median over 11
runs. First row is with this patch, and the second row doesn't call
remove_useless_self_joins and just calls clock_gettime to measure its
with removal 39.61 0.61
no removal 39.45 0.38
So, on this workload, unique self join removal adds about 0.2 mcs, or
0.6% of total time, to query_planner. I also tried a query that joins 26
relations, remove_useless_self_joins takes about 40 mcs. Still, this
time grows quadratically with number of relations we have to process, so
in the final patch I limit it to join_collapse_limit, which brings the
time down to 15 mcs. This is negligible compared to the total
query_planner time, which for 8 relations is about 3 ms, that is, 3
orders of magnitude higher.
These benchmarks mostly measure the path where we don't remove any
joins. I didn't time the join removal itself, because it wins quite some
time by allowing to plan one relation and one join less.
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
|Next Message||Jeremy Finzel||2018-06-25 15:37:18||Some pgq table rewrite incompatibility with logical decoding?|
|Previous Message||Andrew Dunstan||2018-06-25 14:41:41||Re: Using JSONB directly from application|