From: Jesper Krogh <jesper@krogh.cc>
To: Greg Williamson <gwilliamson39@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Message queue table - strange performance drop with changing limit size.
Date: Fri, 01 Jan 2010 16:04:53 +0100
Message-ID: <4B3E0F15.5070706@krogh.cc> (raw)
In-Reply-To: <99179.26367.qm@web46102.mail.sp1.yahoo.com>
References: <4B3DE11B.1010708@krogh.cc>
<99179.26367.qm@web46102.mail.sp1.yahoo.com>
Greg Williamson wrote:
> Jesper --
>
> I apologize for top-quoting -- a challenged reader.
>
> This doesn't directly address your question, but I can't help but
> notice that the estimates for rows is _wildly_ off the actual number
> in each and every query. How often / recently have you run ANALYZE on
> this table ?
It is actually rather accurate, what you see in the explain analyze is
the "limit" number getting in.. where the inner "rows" estiemate is for
the where clause+filter.
> Are the timing results consistent over several runs ? It is possible
> that caching effects are entering into the time results.
Yes, they are very consistent. It have subsequently found out that it
depends on the amount of "workers" doing it in parallel. I seem to top
at around 12 processes.
I think I need to rewrite the message-queue stuff in a way that can take
advantage of some stored procedures instead. Currenly it picks out the
"top X" randomize it in the client picks one and tries to "grab" it. ..
and over again if it fails. When the select top X begins to consume
signifcant time it self the process bites itself and gradually gets worse.
The workload for the individual jobs are "small". ~1-2s.
--
Jesper
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jesper@krogh.cc, gwilliamson39@yahoo.com
Subject: Re: Message queue table - strange performance drop with changing limit size.
In-Reply-To: <4B3E0F15.5070706@krogh.cc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox