On 7/20/16 4:17 PM, Joe Hellerstein wrote:
I heartily concur with this assessment FWIW. I generally use views or WITH clauses myself when building up these kinds of pipelines.
Note that WITH forces materialization. It's almost as bad as temp tables.
In my experience, the PostgreSQL optimizer is not great at collapsing views though. So caveat emptor. Users should be made very aware of EXPLAIN!
Yeah, it does depend on how complicated things are. It's certainly not a magic bullet. :/
The good news is that it's easy to force the planner not to get cute if instead of
SELECT ... FROM view you do SELECT ... FROM (SELECT * FROM view OFFSET 0) view; -- 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) mobile: 512-569-9461
