Re: Index over only uncommon values in table

Steven Schlansker-3 wrote
> The code may get changed multiple times in the same day, if I am
> busy hacking on it.

On the production table???

The other thought-line is just use a (primary key, version) index and make
use LIMIT / OFFSET with an ORDER BY on the PK and the filter on version
AFTER the initial rows are selected.

UPDATE tbl SET ....
FROM (
WITH possible_set AS ( SELECT pkid, version FROM tbl ORDER BY pkid DESC
LIMIT 5000 OFFSET 10000 )
SELECT pkid FROM possible_set WHERE version <> 'CURRENT_VERSION'
) src
tbl.pkid = src.pkid
;

DESC pkid means that newer records are prioritized over older ones.

Probably want a table to keep track of which ranges have already been
reserved and/or updated then client can connect and reserve a range and
perform an update. Concurrency and failure modes would need attention. You
can also keep track on entire ranges and identify which version all the
records are at and select ranges based upon how far behind the current
version they are (or whatever priority algorithm you desire - including
manual tweaks).

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Index-over-only-uncommon-values-in-table-tp5759735p5759765.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.