Jesper Krogh wrote:
> So what I see is that "top 10" takes < 1ms, top 50 takes over 500 times
> more, and top 1000 only 1.5 times more than top 50.
> What can the reason be for the huge drop between limit 10 and limit 50 be?
>
Normally this means you're hitting much higher performing cached
behavior with the smaller amount that's degrading significantly once
you've crossed some threshold. L1 and L2 CPUs caches vs. regular RAM,
shared_buffers vs. OS cache changes, and cached in RAM vs. read from
disk are three transition spots where you can hit a large drop in
performance just by crossing some boundary, going from "just fits" to
"doesn't fit and data thrashes around". Larger data sets do not take a
linearly larger amount of time to run queries against--they sure can
degrade order of magnitude faster than that.
> Indexes:
> "job_funcid_key" UNIQUE, btree (funcid, uniqkey)
> "funcid_coalesce_priority" btree (funcid, "coalesce", priority)
> "funcid_prority_idx2" btree (funcid, priority)
> "job_jobid_idx" btree (jobid)
>
There may very well be an underlying design issue here though. Indexes
are far from free to maintain. You've got a fair number of them with a
lot of redundant information, which is adding a significant amount of
overhead for questionable gains. If you added those just from the
theory of "those are the fields combinations I search via", you really
need to benchmarking that design decision--it's rarely that easy to
figure out what works best. For example, if on average there are a
small number of things attached to each funcid, the middle two indexes
here are questionable--it may be more efficient to the system as a whole
to just grab them all rather than pay the overhead to maintain all these
indexes. This is particularly true if you're deleting or updating
entries ito remove them from this queue, which is going to add a lot of
VACUUM-related cleanup here as well.
In your situation, I might try dropping both funcid_coalesce_priority
and then funcid_prority_idx2 and watching what happens to your
performance and plans, just to learn more about whether they're really
needed. A look at the various pg_stat_* view to help determine what
physical I/O and index use is actually going on might be useful too.
--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.com
