Output: CASE (generate_series(1, 10000000)) WHEN 1 THEN 'First'::text ELSE 'Not First'::text END Uses this new thing called a ProjectSet executor and takes ~1 second Aggregate (cost=32.52.32.53 rows=1 width=8) (actual time=1036.048.1036.048 rows=1 loops=1) Here is a quick test I did to compare the results and timing:įROM (SELECT CASE generate_series(1,10000000) WHEN 1 THEN 'First' ELSE 'Not First' END) AS f It should be noted though that, the new approach if you wanted to do something kinda crazy like that does seem to be faster than the standard approach. But that's still beingĭiscussed, and the code would reside in places not touched here, so that's Likely to instead prohibit SRFs in those places. That's because the SRFs areĮvaluated in a separate ProjectSet node. "arm" of the expression is not evaluated. Returning multiple rows from the expression, even when the SRF containing Not because it's particularlyĭesirable, but because it ends up working and there seems to be no argumentĬurrently the behavior for COALESCE and CASE containing SRFs has changed, That it's a side effect of other optimizations and introduction of new ProjectSet executor node.Īs a side effect, the previously prohibited case of multiple set returningĪrguments to a function, is now allowed. The answer seems reading from the commit notes, Why would I ever do that instead of something like this which will work in all versions? SELECT CASE i WHEN 1 THEN 'First' ELSE 'Not First' END Interesting, right, but how is this useful? In PostgreSQL 9.6 and below, you are slapped with an error message: ERROR: set-valued function called in context that cannot accept a set I feel better now.Generate_series with CASE WHEN SELECT CASE generate_series(1,10) WHEN 1 THEN 'First' ELSE 'Not First' END It's like building a road based on where a driverless truck is going. In which case you're designing your database to suit my other gripe - the query generator. That of course assumes that your query generator can be told to use the mview instead. Then create a view that selects from the materialized view or denormalized table. ![]() If a query like this is run often enough, I would suggest that a materialized view be created, or a table with triggers to store company_code and location_code in the same row as the fact information. In PostgreSQL, that means a BitmapAnd combination of several indexes, tricky to accomplish and it wouldn't apply to this query because there's no filtration of the Fact table other than the existence of reference rows in the two dimensions.existence which is guaranteed by the model.so no filtration whatsoever.ĭata Warehouse applications should feel free to denormalize data to suit reporting needs, provided they keep the denormalized data consistent with the rest of the DW (obviously the whole DW is a little behind the authoritative data source, I'm saying the DW just needs to be consistent with itself). Oracle gets around this with "bitmap join" indexes, but building those are incredibly slow.įact/Dim queries are very heavily reliant on the the query optimizer being able to see the "star join". Everything has to go through synthetic keys. Neither of which is the fault of the poster, obviously.įact/Dimension seems almost designed to foil the ability to put useful indexes on the Fact table. ![]() This post demonstrates two of my biggest complaints in the RDBMS world: query generators and the Kimball Fact/Dimension model.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |