From: | Reg Me Please <regmeplease(at)gmail(dot)com> |
---|---|
To: | Jeremiah Jahn <jeremiah(dot)jahn(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Thanx for 8.3 |
Date: | 2009-01-09 15:25:38 |
Message-ID: | 200901091625.39205.regmeplease@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mohamed | 2009-01-09 15:30:56 | Re: Adding Arabic dictionary for TSearch2.. to_tsvector('arabic'...) doesn't work.. |
Previous Message | Jeremiah Jahn | 2009-01-09 14:46:51 | Re: Thanx for 8.3 |