Thread: order of evaluation of search arguments
As I understand it the order the of evaluation of search arguments is up to the optimizer. I've tested the following query, that is supposed to take advantage of advisory locks to skip over rows that are locked by other consumers running the exact same query and it seems to work fine. It seems to me the optimizer should always prefer to scan by status. What say you?
select *
from queue q
where
q.status = 'unprocessed'
and pg_try_advisory_xact_lock(q.qid, q.tableoid::int) = true
limit 2 -- specify batch size here
for update;
begin;
from queue q
where
q.status = 'unprocessed'
and pg_try_advisory_xact_lock(q.qid, q.tableoid::int) = true
limit 2 -- specify batch size here
for update;
-- now process the queue items
Kiriakos
Kiriakos Georgiou <kg.postgresql@olympiakos.com> writes:
> As I understand it the order the of evaluation of search arguments is up to the optimizer. I've tested the following
query,that is supposed to take advantage of advisory locks to skip over rows that are locked by other consumers running
theexact same query and it seems to work fine. It seems to me the optimizer should always prefer to scan by status.
Whatsay you?
"When it breaks, you get to keep both pieces." Was your testing even
capable of noticing the problem if the query locked more rows than you
wanted?
Less dangerous coding practices might involve putting the lock function
in an outer query, while using an OFFSET 0 in the sub-query as an
optimization fence.
regards, tom lane
I tested it by visual inspection of advisory locks in pg_locks; once with a small test table, and once on a larger 'operations'table in our test environment. It seemed to work, but I hear you, I don't like to depend on the mood of theoptimizer. The drawback of the subquery version is that if the queue table gets big (eg: consumers are shut off) it willspend a lot of time building a worktable only to get a few items from it later in the outer query. Perhaps putting alimit in the inner query as well can alleviate that, like: LIMIT (expected number of consumers) * (batch size) There is a theoretical race condition with this approach (fast vs slow consumers) but it's harmless - a consumer that executesthe inner query slowly may get less or no items to process, although there might be some, but they will picked upeventually, so it's no big deal. thanks, Kiriakos On Feb 16, 2012, at 12:44 AM, Tom Lane wrote: > Kiriakos Georgiou <kg.postgresql@olympiakos.com> writes: >> As I understand it the order the of evaluation of search arguments is up to the optimizer. I've tested the followingquery, that is supposed to take advantage of advisory locks to skip over rows that are locked by other consumersrunning the exact same query and it seems to work fine. It seems to me the optimizer should always prefer to scanby status. What say you? > > "When it breaks, you get to keep both pieces." Was your testing even > capable of noticing the problem if the query locked more rows than you > wanted? > > Less dangerous coding practices might involve putting the lock function > in an outer query, while using an OFFSET 0 in the sub-query as an > optimization fence. > > regards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general