Search code examples
postgresqlherokupsycopg2

Postgres server-side cursor with LEFT JOIN does not return on Heroku PG

I have a Heroku app that uses a psycopg server-side cursor together with a LEFT JOIN query running on Heroku PG 13.5.

The query basically says “fetch items from one table, that don’t appear in another table”.

My data volume is pretty stable, and this has been working well for some time.

This week these queries stopped returning. In pg_stat_activity they appeared as active indefinitely (17+ hours), similarly in heroku pg:ps. There appeared to be no deadlocks. All the Heroku database metrics and logs appeared healthy.

If I run the same queries directly in the console (without a cursor) they return in a few seconds.

I was able to get it working again in the cursor by making the query a bit more efficient (switching from LEFT JOIN to NOT EXISTS; dropping one of the joins).

My questions are:

  • Why might the original query perform fine in the console, but not return with a psycopg server-side cursor?
  • How might I debug this?
  • What might have changed this week to trigger the issue?

I can say that:

  • However I write the query (LEFT JOIN, Subquery, NOT EXISTS), the query plan involves a Nested Loop Anti Join
  • I don’t believe this is related to the Heroku outage the following day (and which didn’t affect Heroku PG)
  • Having Googled extensively, the closest thing I can find to a hypothesis to explain this is a post on the PG message boards from 2003 entitled left join in cursor where the response is “Some plan node types don't cope very well with being run backwards.”

Any advice appreciated!

Solution

  • If you are using a cursor, PostgreSQL estimates that only 10% of the query result will be fetched quickly and prefers plans that return the first few rows quickly, at the expense of the total query cost.

    You can disable this optimization by setting the PostgreSQL parameter cursor_tuple_fraction to 1.0.