Skip to main content
Database Administrators

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

8
  • Thank you for taking the time to look at the question. I read and re-read your answer and I still don't understand the explanation given the symptoms. The structure of the jsonb payload is fixed for each "type" value. In this case every row in the table where type = 8 has and integer array at properties -> 'r'. The proof of that is that when the index is dropped, the same query finishes successfully. That means that for every single row where type = 8 the function is able to parse and interpret the jsonb structure as integer[]. Am I missing something? Commented Mar 4, 2020 at 16:50
  • Additionally, the error points to the fact that the parse failed on a row where the index condition is not met, as I added in my first edit. That to me means that when index is taken into consideration the condition is not checked. Is that the case maybe? Now I am more confused. Commented Mar 4, 2020 at 16:54
  • I edited the question to reflect the fact that json structure is homogeneous per type value in terms of its "schema". Commented Mar 4, 2020 at 17:25
  • So do you mean to say that "Bitmap Heap Scan on test1" is done on the whole table without taking the original query condition type = 8 into consideration? Commented Mar 4, 2020 at 17:29
  • You are getting closer. The function will be called for some (not all) rows that are not indexed, namely rows that are in the same 8 KB block as an indexed row, when the bitmap is lossy. The index is fine, it only has the correct rows indexed. Commented Mar 5, 2020 at 6:51