Re: [PERFORM] maintain_cluster_order_v5.patch

ph...@apra.asso.fr wrote: > Hi Jeff, >> If you can help (either benchmark work or C coding), try reviving the >> features by testing them and merging them with the current tree. > OK, that's the rule of the game in such a community. > I am not a good C writer, but I will see what I could do. The F

Re: [PERFORM] Random penalties on GIN index updates?

Robert Haas wrote: > On Wed, Oct 21, 2009 at 2:35 PM, Tom Lane wrote: >> Jesper Krogh writes: >>> What I seems to miss a way to make sure som "background" application is >>> the one getting the penalty, so a random user doing a single insert >>> won't get stuck. Is that doable? >> You could force

Re: [PERFORM] Random penalties on GIN index updates?

On Wed, Oct 21, 2009 at 2:35 PM, Tom Lane wrote: > Jesper Krogh writes: >> What I seems to miss a way to make sure som "background" application is >> the one getting the penalty, so a random user doing a single insert >> won't get stuck. Is that doable? > > You could force a vacuum every so often

Re: [PERFORM] optimizing query with multiple aggregates

So you've got a query like: SELECT SUM(CASE WHEN field >= 0 AND field < 10 THEN 1 ELSE 0 END) as zeroToTen, SUM(CASE WHEN field >= 10 AND field < 20 THEN 1 ELSE 0 END) as tenToTwenty, SUM(CASE WHEN field >= 20 AND field < 30 THEN 1 ELSE 0 END) as tenToTwenty, ... FROM b

Re: [PERFORM] optimizing query with multiple aggregates

On Wed, Oct 21, 2009 at 6:51 PM, Doug Cole wrote: > > repeated across many different x,y values and fields to build out several > histograms of the data. The main culprit appears to be the CASE statement, > but I'm not sure what to use instead. I'm sure other people have had > similar queries a

Re: [PERFORM] optimizing query with multiple aggregates

On Wed, Oct 21, 2009 at 5:39 PM, Merlin Moncure wrote: > > On Wed, Oct 21, 2009 at 6:51 PM, Doug Cole wrote: > > I have a reporting query that is taking nearly all of it's time in aggregate > > functions and I'm trying to figure out how to optimize it.  The query takes > > approximately 170ms whe

Re: [PERFORM] optimizing query with multiple aggregates

On Wed, Oct 21, 2009 at 6:51 PM, Doug Cole wrote: > I have a reporting query that is taking nearly all of it's time in aggregate > functions and I'm trying to figure out how to optimize it.  The query takes > approximately 170ms when run with "select *", but when run with all the > aggregate funct

[PERFORM] optimizing query with multiple aggregates

I have a reporting query that is taking nearly all of it's time in aggregate functions and I'm trying to figure out how to optimize it. The query takes approximately 170ms when run with "select *", but when run with all the aggregate functions the query takes 18 seconds. The slowness comes from o

Re: [PERFORM] There is a statistic table?

On Wed, Oct 21, 2009 at 11:17 AM, Scott Carey wrote: > > > > On 10/15/09 11:27 PM, "Albe Laurenz" wrote: > >> waldomiro wrote: >>> I need to know how much the postgres is going to disk to get >>> blocks and how much it is going to cache? witch is the >>> statistic table and what is the field that

Re: [PERFORM] Are unreferenced TOASTed values retrieved?

William Blunn writes: > Will the query engine retrieve the entire row (including 10 megabytes of > out-of-line TOASTed data) for every row, and then pick out column > "name", and take an age to do so, No. That's pretty much the whole point of the TOAST mechanism; out-of-line values are not fet

Re: [PERFORM] Random penalties on GIN index updates?

Jesper Krogh writes: > What I seems to miss a way to make sure som "background" application is > the one getting the penalty, so a random user doing a single insert > won't get stuck. Is that doable? You could force a vacuum every so often, but I don't think that will help the locking situation.

[PERFORM] Are unreferenced TOASTed values retrieved?

Hi guys, Imagine if you will that I have a table thus CREATE TABLE "lumps" ( "id"SERIAL PRIMARY KEY, "name"TEXT NOT NULL, "data"BYTEA NOT NULL ); Imagine I have stored say 1000 rows. In each row, we have stored on average 20 bytes in column "name", 10 megabytes in column "

Re: [PERFORM] Finding rows in table T1 that DO NOT MATCH any row in table T2

On 10/21/09 4:52 AM, "Shaul Dar" wrote: > Tom, > > 1. Actually I just tested you suggestion > > SELECT COUNT (*) FROM T1 where NOT EXISTS > (SELECT 1 FROM T2 where T1.PK = T2.FK ) > > and in worked in PG 8.3.8. On a DB with 6M T1 records and 5M T2 records it >

Re: [PERFORM] Random penalties on GIN index updates?

Tom Lane wrote: > jes...@krogh.cc writes: >> If i understand the technicalities correct then INSERT/UPDATES to the >> index will be accumulated in the "maintainance_work_mem" and the "user" >> being unlucky to fill it up will pay the penalty of merging all the >> changes into the index? > > You ca

Re: [PERFORM] maintain_cluster_order_v5.patch

Hi Jeff, >> Hi all, >> >> The current discussion about "Indexes on low cardinality columns" let >> me discover this >> "grouped index tuples" patch (http://community.enterprisedb.com/git/) >> and its associated >> "maintain cluster order" patch >> (http://community.enterprisedb.com/git/maintain

Re: [PERFORM] There is a statistic table?

On 10/15/09 11:27 PM, "Albe Laurenz" wrote: > waldomiro wrote: >> I need to know how much the postgres is going to disk to get >> blocks and how much it is going to cache? witch is the >> statistic table and what is the field that indicates blocks >> reads from the disk and the memory cache? >

Re: [PERFORM] Random penalties on GIN index updates?

jes...@krogh.cc writes: > If i understand the technicalities correct then INSERT/UPDATES to the > index will be accumulated in the "maintainance_work_mem" and the "user" > being unlucky to fill it up will pay the penalty of merging all the > changes into the index? You can turn off the "fastupdate

[PERFORM] Random penalties on GIN index updates?

Hi (running PG8.4.1) As far as I have gotten in my test of PG Full Text Search.. I have got over 6m documents indexed so far and the index has grown to 37GB. The systems didnt do any autovacuums in the process but I manually vacuumed a few times and that stopped growth for a short period of time.

Re: [PERFORM] Finding rows in table T1 that DO NOT MATCH any row in table T2

Tom, 1. Actually I just tested you suggestion SELECT COUNT (*) FROM T1 where NOT EXISTS (SELECT 1 FROM T2 where T1.PK = T2.FK ) and in worked in PG 8.3.8. On a DB with 6M T1 records and 5M T2 records it took 1m8s, My suggestion, i.e. SELECT COUNT(*) FROM T1 LEFT