Re: 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