I will be construct a local lab trying to reproduce this error, because today occur only in client machine.
At the moment my only default is the memory consumed by the Sort plan, because when I excluded the clauses I saw in EXPLAIN ANALYZE consuming memory, the query worked.
Ha, I have one more guest, if PostgreSQL can't access disk memory after consume or estimate more than available in work_mem, maybe this cause the error. I don't know, just a guest.
When I reproduce the error in lab I share in here more infos.
PG Bug reporting form <noreply@postgresql.org> writes: > I have complex queries in large tables that use memory for sorting items, we > upgrate PostgreSQL from 9.1 to 9.4 and in this version we get this error: > 'could not find pathkey item to sort'
That's a fairly common visible symptom for erroneous construction of a plan inside the planner. It has nothing directly to do with memory consumption, although it's possible that changing work_mem would encourage the planner to choose a different plan shape that avoids the bug.
Another likely explanation for the problem appearing and disappearing is that it could be sensitive to the current statistics for the table(s) the query is on, in which case a background auto-analyze might be enough to flip you from the plan shape with the problem to the plan shape without.
Anyway, we'd certainly be interested to fix the problem if you can provide a self-contained, reproducible test case. I'd suggest trying to generate some dummy data that the query fails on.