Re: Improve OR conditions on joined columns (common star schema problem)

On 2/12/17 5:06 PM, David Rowley wrote:
> Yet I've worked with OLTP applications
> since 2005, and I struggle to recall any many:many joins at all.

Interesting... I've run across it numerous times. In any case, for OLTP
there's other things you can do fairly easily.

> Perhaps this optimisation is a candidate for only being applied when
> some sort of planner_strength GUC (as mentioned in FOSDEM developer
> meeting in 2016) reaches some threshold. There's certainly already
> some planner smarts that can be skipped when such a GUC is set to a
> lower level (e.g join removal). We could likely save many cycles if we
> had the ability to re-plan queries where total_cost > X with more
> smarts enabled.

Yeah, I strongly suspect some kind of "multi-stage" planner would be a
big win.

As for the POC, that's the same kind of plan I'm seeing IRL: a nested
loop gets used essentially to do the lookup of dimension text to
dimension ID.

I'm wondering if there's any tricks that could be applied on the sort
since it's dealing with CTIDs.

I do think it'd be even better if we had the ability to do that lookup
as part of planning, so you could discover the exact stats for the
relevant ID values, but that's even more involved.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)