On Friday 09 January 2009 15:46:51 Jeremiah Jahn wrote: > On Fri, 2009-01-09 at 08:17 +0100, Reg Me Please wrote: > > On Friday 09 January 2009 00:10:53 Jeremiah Jahn wrote: > > > Just wanted to say thank you for version 8.3. > > > > > > The ordered indexing has dropped some of my search times from over 30 > > > seconds to 3. I've been beating my head against this issue for over 8 > > > years. I will drink to you tonight. > > > > > > thanx again, > > > -jj- > > > > > > > > > > > > -- > > > When you're dining out and you suspect something's wrong, you're > > > probably right. > > > > Give also CLUSTER a try. > > And partial indexes also. > > I've had clusters going since they became available. They still required > massive sequential scans and with a dedicated disk array w/ a sustained > I/O rate of 600MB/s it still took 30 seconds. My data has about 250000 > new/updated entries per day, so the clusters just couldn't keep up. 70% > of my problem was sorting, followed by a complex join. Now that the > sorting is O(n), I've modified things to use a search table that is > basically a select into of the join I always had to do. Had I done this > before, I wouldn't have had the improvements to justify the added > complexity to my system. > > I use partial indexes in other places, but these are name searches where > someone wants all the 'SMITHS%' in half the state of Illinois who've > been 'convicted' of 'aggravated battery' 'in the last 5 years' and have > traffic tickets'; It's difficult to come up with partials when the > queries are not predictable. > > Nor have I ever had the budget to get enough memory to keep these tables > in memory. There just always been a limit to the amount of > hardware(money) I can throw as something. Of course that's what makes it > fun and challenging. > > Now if there was just simple way to make some sort of persistent view > that could have indexes on it, so that complex joins could be sped up, > in stead of making non-normal tables. (hint hint :) > > > Prosit! > > > > -- > > Fahrbahn ist ein graues Band > > weisse Streifen, grüner Rand For materialized view just google it. Or just jump here: http://www.jonathangardner.net/tech/w/PostgreSQL/Materialized_Views -- -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
