Oleg Bartunov <obartunov@xxxxxxxxx> writes: > On Sun, Nov 13, 2016 at 6:05 PM, Aaron Lewis <the.warl0ck.1989@xxxxxxxxx> >> It takes 500ms with 10m rows, could it be faster? > sure. Recheck with function call is pretty expensive, so I'd not recommend > to create functional index, just create separate column of type tsvector > (materialize to_tsvector) and create gin index on it. You should surprise. I doubt it'll help that much --- more than half the time is going into the bitmap indexscan, and with over 1m candidate matches, there's no way that's going to be super cheap. I wonder whether a gist index would be better here, since it would support a plain indexscan which should require scanning much less of the index given the small LIMIT. (Materializing the tsvector would probably help for gist, too, by reducing the cost of lossy-index rechecks.) BTW, it still looks like the performance is being significantly hurt by inadequate work_mem. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
