A Valuable Lesson... WITH qry AS () ... Update

tbl FROM qry...
Was being rewritten by the optimizer. With HORRIBLE performance, hence the WITH Query.

In The end, we were left with 2 approaches. Create a Temporary Table. And update from that.
This reduced the time to 3 minutes for the temp table creation and about 45 Seconds for the update. (Over 1 million rows).

But the ORIGINAL WITH was running into HOURS (3.5 when I let it run overnight). Same rows, same data.

Today, we found WITH qry AS MATERIALIZED ()...
That was MORE in line with expectations. While about 30% slower than the temp table approach, it was fast enough with less complexity... That we will use this approach on this query...

The cause is a single function that looks up a value, that takes 50ms on every call. The challenge is that there are 7,000 lookups.
But there are over 1 Million assignments of these values. The rewrite was causing it to be called once per row.

In the form of a question. Would RADICALLY CHANGING the COST of that function to be really high have forced the optimizer to NOT unwind the WITH statement (and materialize it)... Or is this just a known thing when reworking queries???

Thanks in advance.

> here I loaded the explain Plans These EXPLAINs are worthless. Why didn't you try the script I attached (or, at least, looked at those)? I am not going to look at anything until all the requested information is provided. Why did you use (or at least looked at) the provided script? Some random comments about the other stuff: . For the tables schema — https://wiki.postgresql.org/wiki/Don%27t_Do_This . Count how many of the things listed there you did. . For the function — it's not actually IMMUTABLE, you should never lie to PostgreSQL about that (otherwise you might be "rewarded" with incorrect query results, and nobody (no postgres hacker) is going to have any sympathy for you). > Let me add that I NEED the MATERIALIZED View version (or rather, for speed, it matters). Well, I don't see any actual proof of that, by now. > And it is a simple lookup to a table, that is indexed (if not overly indexed). No, it is not? As WHERE <constant> BETWEEN a_column and another_column cannot effectively use a b-tree index in any RDBMS, no? > I am truly curious how I tell PG to avoid calling this function without a MATERIALIZED view. Start with not lying to PostgreSQL about its volatility category. ;)

Also... why you don't use the appropriate types for IP addresses ( https://www.postgresql.org/docs/current/datatype-net-types.html )?

First, YES, I tried a LOT of things, including lying about the STABILITY of the function. For testing. It only matters to learn how the system responds (I know the risks, and will not INDEX based on this, and will NOT carry this into production). Second, the INET type... I get that PG has a different type for this. And IF (and ONLY IF) I am forced to convert to this type, do I have the clearance to make such a change to the system. Sorry, I cannot just play God here. Every change has implications in many places. (This is a converted system). But neither of these things are pertinent to the problem at hand. I don't care what this function is doing. I accept that it is what it is. I am TRYING TO LEARN how to "encourage" PG to work around a potentially slow result. You said there was a 3rd approach. The individual query uses the index to answer the question for this function. Regardless... (Next reply will be about your script)

First, I don't consider EXPLAINS and the QUERIES, and the Tables to be worthless. But that's fine. I did not run your script, because it was a bit complex, and it executes against my DB. I have to understand what it might do first. My DB has a specific state, matching another system. Applying a change (or set of changes) means I have to be prepared to REVERT them. Second. You are actually NOT just doing an EXPLAIN... You are EXECUTING my query... Do you EXPECT me to run a 3.5 (or 4.5 HOUR LONG query)? I am glad to help you PERFECT the script. I think it is actually a great idea! But I did not expect it to run the query. I thought it was going to do an explain plan, and maybe PARSE the query. Honestly. The query takes HOURS. I will adjust the data to be a smaller set of the data... Again, I TRULY appreciate the help. And I LOVE the idea having a script that helps people. But this is a query that updates data, and it should be made clear it will EXECUTE that query! I will see if I can reduce the impact and get your results. The one thing I did not give you was that I am on Version 14.5 (Ubuntu)