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) -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers