Re: [GENERAL] Postgresql vs. aggregates
Hi, - Original Message - From: "Richard Huxton" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: "Scott Marlowe" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, June 10, 2004 8:03 AM Subject: Re: [GENERAL] Postgresql vs. aggregates > [EMAIL PROTECTED] wrote: > > > But that raises an interesting idea. Suppose that instead of one > > summary row, I had, let's say, 1000. When my application creates > > an object, I choose one summary row at random (or round-robin) and update > > it. So now, instead of one row with many versions, I have 1000 with 1000x > > fewer versions each. When I want object counts and sizes, I'd sum up across > > the 1000 summary rows. Would that allow me to maintain performance > > for summary updates with less frequent vacuuming? > > Perhaps the simplest approach might be to define the summary table as > containing a SERIAL and your count. > Every time you add another object insert (nextval(...), 1) > Every 10s summarise the table (i.e. replace 10 rows all "scored" 1 with > 1 row scored 10) > Use sum() over the much smaller table to find your total. > Vacuum regularly. > Something along these lines except using a SUM instead of a COUNT. http://archives.postgresql.org/pgsql-performance/2004-01/msg00059.php Nick ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Postgresql vs. aggregates
[EMAIL PROTECTED] wrote: But that raises an interesting idea. Suppose that instead of one summary row, I had, let's say, 1000. When my application creates an object, I choose one summary row at random (or round-robin) and update it. So now, instead of one row with many versions, I have 1000 with 1000x fewer versions each. When I want object counts and sizes, I'd sum up across the 1000 summary rows. Would that allow me to maintain performance for summary updates with less frequent vacuuming? Perhaps the simplest approach might be to define the summary table as containing a SERIAL and your count. Every time you add another object insert (nextval(...), 1) Every 10s summarise the table (i.e. replace 10 rows all "scored" 1 with 1 row scored 10) Use sum() over the much smaller table to find your total. Vacuum regularly. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Postgresql vs. aggregates
[EMAIL PROTECTED] writes: > I'd find all this much easier to reason about if I understood how > the versions of a row are organized and accessed. How does postgresql > locate the correct version of a row? It doesn't, particularly. A seqscan will of course visit all the versions of a row, and an indexscan will visit all the versions matching the indexscan key-column conditions. It's up to the "time qualification" tests (tqual.c) to accept only the version that is visible to your transaction. There are two or three implementation choices in the btree index routines that are intended to increase the chances that you'll hit the version you want sooner rather than later. But they're only heuristics. The bottom line is that we check all the versions till we find the right one. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
