PostgreSQL query very slow with limit 1

submited by

Style Pass

2022-07-06 11:00:11

This query is very slow when there are no values for a given objectID (it is fast if there are results). If I remove the limit it tells me nearly instantaneous that there are no results:

An explain shows me that the query without limit uses the index, where as the query with limit 1 does not make use of the index:

The table contains 44,884,559 rows and 66,762 distinct objectIDs. I have separate indexes on both fields: timestamp and objectID. I have done a vacuum analyze on the table and I have reindexed the table.

In general I assume it has to do with the planner making wrong assumptions about the exectution costs and therefore chooses for a slower execution plan.

You're running into an issue which relates, I think, to the lack of statistics on row correlations. Consider reporting it to pg-bugs for reference if this is using the latest version Postgres.

limit 1 makes Postgres look for a single row, and in doing so it assumes that your object_id is common enough that it'll show up reasonably quickly in an index scan.