Re: [PERFORM] Speeding up JSON + TSQUERY + GIN

On 06.03.2017 05:25, Jeff Janes wrote:

Bitmaps can overflow and drop the row-level information, tracking only the blocks which need to be inspected.  So it has to have a recheck in case that happens (although in your case it is not actually overflowing--but it still needs to be prepared for that).

Good to know.

Also, I think that jsonb_path_ops indexes the hashes of the paths, so it can deliver false positives which need to be rechecked.

Wow, that's a very important piece of information. It explains a lot. Thanks a lot.

And you are selecting `id`, which is not in the index so it would have to consult the table anyway to retrieve that.  Even if it could get all the data from the index itself, I don't think GIN indexes support that feature.

Yes, I see. I actually was sloppy about the query. What's really important here would be counting the number of rows. However, from what I can see, it's the best PostgreSQL can do right now.


Or you have any more ideas how to speed up counting?


Best,
Sven