Re: Speeding up JSON + TSQUERY + GIN

"Sven R. Kunze" <srkunze@xxxxxxx> · Thu, 2 Mar 2017 22:19:49 +0100



    On 01.03.2017 18:04, Jeff Janes wrote:

    
          On Wed, Mar 1, 2017 at 6:02 AM, Sven
            R. Kunze <srkunze@xxxxxxx>
            wrote:

            
                  On
                    28.02.2017 17:49, Jeff Janes wrote:

                  
                        Oh.  In my hands, it
                          works very well.  I get 70 seconds to do the
                          {age: 20} query from pure cold caches, versus
                          1.4 seconds from cold caches which was
                          followed by pg_prewarm('docs','prefetch').
                          

                          How much RAM do you have?  Maybe you
                            don't have enough to hold the table in RAM. 
                            What kind of IO system?  And what OS?

                          
                 On my test system:

                
                RAM: 4GB

                IO: SSD (random_page_cost = 1.0)

                OS: Ubuntu 16.04

              
            4GB is not much RAM to be trying to pre-warm this
              amount of data into.  Towards the end of the pg_prewarm,
              it is probably evicting data read in by the earlier part
              of it.
            

            What is shared_buffers?

            
    942MB.

    
    But I see where you are coming from. How come that these queries
    need a Recheck Cond? I gather that this would require reading not
    only the index data but also the table itself which could be huge,
    right?

    
    Sven