Re: [PERFORM] Help with rewriting query
[Kevin Grittner - Fri at 02:49:57PM -0500] > If you add a column to the person table for "last_food_id" and triggers > to maintain it when the food table is modified, voila! You have a > simple and fast way to get the results you want. Reminds me about the way the precursor software of our product was made, whenever it was needed to check the balance of a customer, it was needed to scan the whole transaction table and sum up all transactions. This operation eventually took 3-4 seconds before we released the new software, and the customers balance was supposed to show up at several web pages :-) By now we have the updated balance both in the customer table and as "post_balance" in the transaction table. Sometimes redundancy is good. Much easier to solve inconsistency problems as well :-) -- Tobias Brox, +47-91700050 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] faster search
Steve Atkins wrote: On Fri, Jun 10, 2005 at 01:45:05PM -0400, Clark Slater wrote: Hi- Would someone please enlighten me as to why I'm not seeing a faster execution time on the simple scenario below? > [...] > Create an index on (productlistid, typeid, partnumber) then select * from test where productlistid=3 and typeid=9 order by productlistid, typeid, partnumber LIMIT 15; Clark, try also adding (just for testing) partnumber to your where clause, like this: select * from test where productlistid=3 and typeid=9 and partnumber='foo' order by productlistid, typeid, partnumber; and check output of explain analyze. I had experiences of planner "bad" use of indexes when attribute types were integer and cardinality was low (a single attribute value, like "typeid=9" selects one or few rows). However, this was on 7.1.3, and probably is not relevant to your case. -- Cosimo ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] faster search
John A Meinel <[EMAIL PROTECTED]> writes: > I am a little surprised that it is taking 40s to scan only 400k rows, > though. Yeah, that seemed high to me too. Table bloat maybe? It would be interesting to look at the output of "vacuum verbose test" to see how much dead space there is. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
