On Wed, May 11, 2016 at 11:59 PM, Lucas Possamai <drum.lucas@xxxxxxxxx> wrote: > >> >> How big is the table? The gin index? shared_buffers? RAM? What >> kind of IO system do you have, and how many other things were going on >> with it? > > > - Just a reminder that I'm not running these tests on my prod server.. I'm > running on my test server. So the confs will be different > >> The table is 9GB big >> The gin index is 400MB big >> shared_buffers = 1536MB >> RAM = 8 GB With those sizes, the gin index will probably be naturally kept mostly in the file-system cache, if it is used regularly. So the original slowness of your first query is likely just a cold-cache problem. Can you generate a stream of realistic queries and see what it stabilizes at? > I just wanted to understand why the GIN index is not working, but it works > here: https://www.depesz.com/2011/02/19/waiting-for-9-1-faster-likeilike/ In your first email, the gin index did "work", according to the execution plan. It just wasn't as fast as you wanted. In general, the longer the query string is between the %%, the worse it will perform (until version 9.6, and to a smaller degree even with 9.6). But it still seems oddly slow to me, unless you have a cold-cache and really bad (or overloaded) IO. > >> >> >> It would be interesting to see the output of explain (analyze, >> buffers) with track_io_timing turned on. > > > explain analyze buffer with track_io_timing turned on: ... That is the wrong query. The CTE (i.e. the WITH part) is an optimization fence, so it can't use the gin index, simply because of the way you query is written. (I think Melvin suggested it because he noticed that using the gin index actually slowed down the query, so he wanted to force it to not be used.) Cheers, Jeff -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
