On 4/14/15 1:28 PM, Pawel Veselov wrote:
>
> I wonder if what I need to do, considering that I update a lot of "the
> same" rows as I process this queue, is to create a temp table, update
> the rows there, and then update the actual tables once at the end...
That's what I'd do.
The other option would be to use a constraint trigger paired with a
per-row trigger on the hourly table to drive the daily table, and on the
daily table to drive the total table. The way that would work is the
per-row table would simply keep track of all the unique records that
were changed in a statement (presumably by putting them in a temp
table). Once the statement is "done", the constraint trigger would fire;
it would summarize all the changed data and do a much smaller number of
updates to the table being summarized into.
BTW, you also made a comment about not having to hit the table if you
look at something in an index. You can only do that if all the data you
need is in the index, AND the page with the record is marked as being
all-visible (google for Postgres Visibility Map). If that's not the case
then you still have to pull the row in the table in, in order to
determine visibility. The only case where you can still avoid hitting
the table is something like a NOT EXISTS; if you can't find any entries
in the index for something then they definitely won't be in the table.
But remember that if you update or delete a row, removing it from an
index, the data will stay in that index until vacuum comes along.
Also, there's no point in doing a REINDEX after a VACUUM FULL; vacuum
full rebuilds all the indexes for you.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com