Thanx for 8.3

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 :)

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