Skip to content

Instantly share code, notes, and snippets.

@iseki0

iseki0/Q.sql

Created December 13, 2024 20:44
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save iseki0/ad586684661ada516fde8a3896429a09 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save iseki0/ad586684661ada516fde8a3896429a09 to your computer and use it in GitHub Desktop.
Download ZIP
Q
Raw
Q.sql
drop table if exists queue;
create table queue
(
id bigint not null primary key,
status smallint not null default 0,
last_run timestamptz null default null
);
drop index if exists queue_status_0_idx;
create index queue_status_0_idx on queue (last_run nulls first) where status = 0;
truncate table queue;
DO
$$
BEGIN
FOR i IN 1..10000000
LOOP
-- INSERT INTO queue (id, status) VALUES (i, floor(random() * 5)::int);
INSERT INTO queue (id, status) VALUES (i, 0);
END LOOP;
END
$$;
explain (analyze , timing , verbose ,costs ,buffers)
update queue
set status = 1,
last_run = now()
where id = (select id from queue where status = 0 order by last_run nulls first limit 1 for update skip locked)
returning *;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment