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
