On Nov 21, 2017 00:05, "Matthew Hall" wrote:
> Are all indexes present at the time you insert? It will probably be much
faster to insert without the gin index (at least) and build it after the
load.
There is some flexibility on the initial load, but the updates in the
future will require the de-duplication capability. I'm willing to accept
that might be somewhat slower on the load process, to get the accurate
updates, provided we could try meeting the read-side goal I wrote about, or
at least figure out why it's impossible, so I can understand what I need to
fix to make it possible.
As long as you don't let anyone use the table between the initial load and
when the index build finishes, you don't have to compromise on
correctness. But yeah, makes sense to worry about query speed first.
> If you repeat the same query, is it then faster, or is it still slow?
If you keep the expression exactly the same, it still takes a few seconds
as could be expected for such a torture test query, but it's still WAY
faster than the first such query. If you change it out to a different
expression, it's longer again of course. There does seem to be a
low-to-medium correlation between the number of rows found and the query
completion time.
To make this quick, you will need to get most of the table and most of the
index cached into RAM. A good way to do that is with pg_prewarm. Of
course that only works if you have enough RAM in the first place.
What is the size of the table and the gin index?
Cheers,
Jeff