Re: Speeding up JSON + TSQUERY + GIN

"Sven R. Kunze" <srkunze@xxxxxxx> · Mon, 6 Mar 2017 22:10:46 +0100



    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