Monthly Archives: November 2017

I never understood, why in some cases when you look at the Postgres execution plan you see “Recheck condition”. This happens, when an index search is executed, and when I was looking at the execution plans of that kind, I never could understand, why Postgres wants to go back to the table and “recheck” something, while all the conditions should be satisfied by the index itself.

Last week however, i found myself in non-typical situation, when I had a query to optimize, and I could not bring it to the acceptable speed. If was imperative, that it would execute in less than a minute, actually highly preferable – within 30 sec. I knew that the culprit was one of the sub-selects, which was querying a very large table, and thereby I absolutely had to convince Postgres to use the index-only scan.

And it didn’t work. I’ve created a compound partial index, which would exactly describe a condition I was filtering by, and I’ve included all the fields I needed to select in this index. But the query continued to be slow, and the execution plan was clearly showing “Recheck condition”.

After a number of experiments I’ve realized, that I in order to ensure the index-only scan I need to include into the index not only the attributes I am selecting, but also the attributes which I am constraining.

Specifically, when I was running a query

SELECT loan_id,
min(date) AS first_date
FROM very_large_table
WHERE days_past_due >30 AND status in (1,2)

having the index

CREATE INDEX cond_index ON
very_large_table(loan_id, date, days_past_due)
WHERE days_past_due>30 AND status IN (1,2)

was not enough to ensure the index-only scan. Instead I had to build the following index:

CREATE INDEX cond_index_2 ON
very_large_table(loan_id, date, days_past_due, status)
WHERE days_past_due>30 AND status IN (1,2)

I was wondering for a while – why? But then I sort of thought about this “as a database” and realized, that if the status is not included into the index, then, if for some record only the status will be changed, and all indexed attributes remain the same, the rebuild of the index just won’t be triggered, and thereby the condition will require a check. On the contrary, if the status would change from “something else” to 1 or 2, the index condition will be triggered, and this record will be re-indexed.

Now I will remember!