Re: Experimental evaluation of PostgreSQL's query optimizer

 

I see a parallel with indexing and partial indexes here.

We obviously cannot afford to keep cross-table correlations for every possible pairing of join conditions across every possible set of joined tables. Much like we can't afford to keep indexes for every possible set of columns, but even worse.

Much as we let users CREATE INDEX to tell us what cols to index, maybe we should let them CREATE a cross-table join statistics collector for a particular set of tables, optionally qualified with a filter condition just like we do on partial indexes, and optionally transformed via an immutable expression like we do for expression indexes, e.g.:

CREATE JOIN STATISTICS ON t1 JOIN t2 ON (t1.col1 = t2.col2);

plus a simplified form like

CREATE JOIN STATISTICS ON t1 JOIN t2 USING (somecol);

That way we let an admin who's tuning queries direct effort at problem areas. It's not automagical, but it's an area where tools could analyze pg_stat_statements to direct effort, much like is currently done for index creation. Like index creation I don't think it's practical to do this entirely automatically and behind the scenes since collecting the stats for all possibilities rapidly gets prohibitive.