Hi,
I have a made a small deductive database on top of PostgreSQL for educational/research purposes. In this setting, due to certain VIEW-constructions, queries often end up being self-joins on primary keys, e.g.:
SELECT t1.id, t2.val
FROM t AS t1 JOIN t AS t2 USING (id);
where t(id) is a primary key. This query is equivalent to the much more efficient:
SELECT id, val
FROM t AS t1;
However, PostgreSQL currently does not seem to implement this simplification. Therefore, I have looked into writing an extension that performs this, but I am struggling a bit with finding out when this simplification should be done, i.e. which hook I should implement.
The simplification is not too different from those done in prep/prepjoin.c, i.e. doing the simplification on the query-tree directly. However, I think I then would need to implement a planner_hook, as it is the only hook giving me direct access to the query-tree. But I need to perform my simplification after view-definitions have been expanded into the query, and after the transformations in prepjoin.c (but before the rest of planning). But there seems to be no easy way to inject a function there, as this is buried deep in the middle of the planner-function.
I therefore looked into using a set_join_pathlist_hook, and try to do the simplification at path-level. I.e., doing something like:
static void self_join_optimize_hook(PlannerInfo *root, RelOptInfo* joinrel, RelOptInfo* outerrel, RelOptInfo* innerrel, JoinType jointype, JoinPathExtraData* extra)
{
if (is_selfjoin_on_pk(root, joinrel, extra)) {
ListCell *p;
foreach(p, innerrel->pathlist) {
add_path(joinrel, (Path *) p);
}
}
}
That is, if joinrel is a (binary) self-join on a primary key, the paths for evaluating the join is the same as the paths for evaluating the innerrel, However, this does not work, as the rest of the query may require values from the other table (e.g. t2 in the example above). I therefore need to replace all mentions of t2 with t1, but is this possible at a path-level?
If not, does anyone have a an idea on how this can be done in a different way? Thanks!
Kind regards,
Leif Harald Karlsen
Senior Lecturer
Department of Informatics
University of Oslo