On Mon, Jul 4, 2011 at 11:55 PM, Alban Hertroys <dalroi@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote: > On 5 Jul 2011, at 3:23, David Johnston wrote: > >>> Does anyone have fresh thoughts or suggestions for dealing with >>> INSERT-mostly tables conceived in this manner? > > You're struggling with read-performance in an INSERT-mostly table? Where are your performance priorities, on INSERT or on SELECT? Principally on INSERT, but yet I don't want SELECT to be several orders of magnitude slower than it needs to be (and getting slower, as ndistinct vanishes into a tiny fraction of all records). With procedural code or tricking the optimizer I can convince it to do something reasonable, even if the constants are much higher than they need to be (from things like the index scan having to be restarted all the time, for example). There will be many, many records that are *never* fetched/joined. In this case, this is a monitoring application that is appending large amounts of data, but it needs to join back recent values only when an operator/human being wants to take a look at what has is happening right now. > Setup a materialized view. This rather defeats the point AFAIK, because keeping the materialized view up to date (being more than thirty seconds out of date is not desirable) will be expensive. Maintaining the index on the (key, recency) pair is, in effect, a materialization of sorts already. In any case, as I was saying: there are terrible workarounds for this, but I think this is a rather common problem with INSERT-mostly relations that effectively want row-versioning of a sort, so I was hoping that lucid solutions to this issue have grown since 2008, when the thread I linked to transpired. -- fdr -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
