I've ran into a bit of a head scratching situation and was hoping that someone with more knowledge that I could help me understand the behaviour I'm seeing. I'm running on PostgreSQL 12.2. I have a SELECT query that returns different results depending on whether statistics for the table have been collected or not.The query uses several CTEs and returns a single integer. This integer changes depending on whether the table has been analyzed. As far as I can tell I am not using any 'volatile' functions in my SELECT query. It took me a while to find a way to reproduce the issue. How I eventually reproduced it was: -- Delete all statistics. DELETE FROM pg_statistic; -- Truncate table and insert values into table. TRUNCATE TABLE target_table; INSERT INTO target_table (...) VALUES (...); -- The results of the SELECT are different depending on whether ANALYZE is called. ANALYZE target_table; -- Run select query (involving several CTEs). SELECT ...; I haven't generated a minimal test case yet, but I did notice that if all CTEs in the SELECT query are defined using AS NOT MATERIALIZED the results are always the same regardless of whether the table has been ANALYZED yet. Could anyone share knowledge about why this is happening? Thanks, James Brauman
