Thread: Thanx for 8.3
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.
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. Prosit! -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand
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
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