Using debugging parameters like enable_nestloop
should not change the results of a query. If it does that'd generally be a bug.
However, you should not use enable_nestloop = off
or similar in production. It's a very big hammer, and while it might appear to fix your immediate problem, it'll create performance problems elsewhere. A nested loop is often the correct plan node for best performance in a part of a query, and forcing PostgreSQL to prefer other options might make queries significantly slower.
Instead, you should generally adjust the cost parameters like random_page_cost
to encourge PostgreSQL to choose a plan that performs better on your hardware and workload. For more information see the tuning advice on the PostgreSQl wiki and in the main documentation.
Probable bug on 9.6 and 9.6.1
This completely looks like a bug to me...
I don't know why it happens, but I can confirm that it happens. This is the simplest found setup that reproduces the problem (in version 9.6.0 and 9.6.1).
CREATE TABLE users
(
id SERIAL PRIMARY KEY,
email TEXT NOT NULL,
column_that_we_will_drop TEXT
) ;
-- Function that uses the previous table, and that has a CTE
CREATE OR REPLACE FUNCTION __post_users
(_useremail text)
RETURNS integer AS
$$
-- Need a CTE to produce the error. A 'constant' one suffices.
WITH something_even_if_useless(a) AS
(
VALUES (1)
)
UPDATE
users
SET
id = id
WHERE
-- The CTE needs to be referenced, if the next
-- condition were not in place, the problem is not reproduced
EXISTS (SELECT * FROM something_even_if_useless)
AND email = _useremail
RETURNING
id
$$
LANGUAGE "sql" ;
After this setup, the next statement just works
SELECT * FROM __post_users('a@b.com');
At this point, we DROP one column:
ALTER TABLE users
DROP COLUMN column_that_we_will_drop ;
This change makes the next statement to generate an error
SELECT * FROM __post_users('a@b.com');
which is the same as mentioned by @Andy:
ERROR: table row type and query-specified row type do not match
SQL state: 42804
Detail: Query provides a value for a dropped column at ordinal position 3.
Context: SQL function "__post_users" statement 1
SELECT * FROM __post_users('a@b.com');
Dropping and recreating the function does NOT solve the problem.
VACUUM FULL (the table or the whole database) does not solve the problem.
The bug report was passed to the appropriate PostgreSQL mailing list and we had a very fast response:
I can't reproduce this in HEAD or 9.6 branch tip. I believe it was
already fixed by this patch, which went in a bit after 9.6.1:
https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=f4d865f22
But thanks for the report!
regards, tom lane
Version 9.6.2
On 2017-03-06, I can confirm that I cannot reproduce this behaviour on version 9.6.2. That is, the bug seems to have been corrected on this release.
UPDATE
Per comment of @Jana: "I can confirm the bug is present in 9.6.1 and was fixed in 9.6.2. The fix is also listed on postgres release website: Fix spurious "query provides a value for a dropped column" errors during INSERT or UPDATE on a table with a dropped column"
Best Answer
It's explained very well in the documentation.
So far, so good, BUT:
So, as pointed out in the example given, if you have a query like this:
So, in this case:
Far better to have:
So that the optimizer can "fold" the CTE query "into" the main query and make use of the
INDEX
on thekey
field ofbig_table
!Re. the
DEFAULT
ofNOT MATERIALIZED
:So the
DEFAULT
isNOT MATERIALIZED
if:otherwise you have to tell PostgreSQL to use
NOT MATERIALIZED
.The only small problem that I see is that testing will be required to see if
NOT MATERIALIZED
is an improvement or not? I can see circumstances where the balance will swing between the two depending on table size, fields selected and indexes on the fields and tables used in the CTE - in other words, there's no substitute for knowledge and experience. The DBA isn't dead and gone yet! :-)