Re: [PERFORM] Speeding up JSON + TSQUERY + GIN - Mailing list pgsql-performance

On 01.03.2017 18:04, Jeff Janes wrote:

On Wed, Mar 1, 2017 at 6:02 AM, Sven R. Kunze <srkunze@mail.de> 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