On May 6, 2011, at 1:39 PM, Tomas Vondra wrote: > Anyway I'd recommend to start with the eager approach, it's much easier > to implement. You can implement the lazy approach later, if you find out > it's needed. With the eager approach, I think I'm too likely to get write conflicts. Thanks for the reference to the paper, I believe that's what I was looking for. > And you should strive to use HOT feature (if you're on >= 8.4), > especially with the eager approach - it often does a lot of updates and > leads to bloat of the aggregated table. So decrease the fillfactor and > do not index the columns that are updated by the triggers. See, that's the kind of info I'm looking for ;-) On May 6, 2011, at 1:59 PM, Andrew Sullivan wrote: > If the WHERE clause is fairly selective and indexed, that should be > fast. Not as fast as estimates based on trigger-written values in > another table, of course, but reasonably fast. So the first order of > business is usually to find or create indexes that will make SELECT on > the same criteria fast. In this case, it depends on the result of a pretty complex join that involves some gnarly time calculations, and finding the unmatched rows from one side of an outer join. I really don't think there's a way to optimize the straight-up query to be faster than it is, I looked at that for a good long time, explain/analyze and all. Postgres is using the appropriate index to narrow things down as much as it can at the very beginning, it just then has to perform a heck of a lot of work to finish the join... And it's not taking ***that*** long--it's just that I want it faster! > It's only unqualified "SELECT count(*)" that is slow. Generally, the > system table is good enough for that, I find. (Someone: "How long > will this take?" Me: "There are about 400 million rows to go > through." Even if you're off by 50 million at that point, it doesn't > matter.) FYI, I have no need for unqualified select count(*) in this app--just doesn't happen, ever ;-) Thanks. -- Scott Ribe scott_ribe@xxxxxxxxxxxxxxxx http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
