I have one shameful query where, unable to convince it to execute a subquery that is essentially a constant for most of the rows outside of the hot loop of the main index scan, I pulled it out into a temp table and had the main query select from the temp table instead. Even creating a temp table and indexing it was faster than the plan the Postgres query planner absolutely insisted on. Things like CTEs etc made no difference, it would still come up with the same dumb plan every way I expressed the query.
The worst thing is not even being able to debug or understand what is going on because you can't influence the query plan to try alternative hypotheses out easily.
I have one shameful query where, unable to convince it to execute a subquery that is essentially a constant for most of the rows outside of the hot loop of the main index scan, I pulled it out into a temp table and had the main query select from the temp table instead. Even creating a temp table and indexing it was faster than the plan the Postgres query planner absolutely insisted on. Things like CTEs etc made no difference, it would still come up with the same dumb plan every way I expressed the query.
The worst thing is not even being able to debug or understand what is going on because you can't influence the query plan to try alternative hypotheses out easily.