Re: [PERFORM] query plan with index having a btrim is different for strings of different length

On Dec 10, 2008, at 4:08 PM, Tom Lane wrote: Richard Yen <[EMAIL PROTECTED]> writes: Is there any way to tune this so that for the common last names, the query run time doesn't jump from <1s to >300s? Well, as near as I can tell there's factor of a couple hundred difference between the freq

Re: [PERFORM] query plan with index having a btrim is different for strings of different length

Richard Yen <[EMAIL PROTECTED]> writes: > Is there any way to tune this so that for the common last names, the query > run time doesn't jump from <1s to >300s? Well, as near as I can tell there's factor of a couple hundred difference between the frequencies of 'smith' and 'smithers', so you shou

Re: [PERFORM] query plan with index having a btrim is different for strings of different length

> > tii=# explain analyze SELECT m_object_paper.id FROM m_object_paper, > m_assignment WHERE m_object_paper.assignment = m_assignment.id AND > m_object_paper.owner=-1 AND m_assignment.class = 2450798 AND > lower(btrim(x_firstname)) = lower(btrim('Jordan')) and > lower(btrim(x_lastname)) = lower(btr

Re: [PERFORM] query plan with index having a btrim is different for strings of different length

On Dec 10, 2008, at 11:39 AM, Robert Haas wrote: You guys are right. I tried "Miller" and gave me the same result. Is there any way to tune this so that for the common last names, the query run time doesn't jump from <1s to >300s? Thanks for the help! Can you send the output of EXPLAIN A

Re: [PERFORM] query plan with index having a btrim is different for strings of different length

On Dec 10, 2008, at 11:34 AM, Tom Lane wrote: Richard Yen <[EMAIL PROTECTED]> writes: You guys are right. I tried "Miller" and gave me the same result. Is there any way to tune this so that for the common last names, the query run time doesn't jump from <1s to >300s? If the planner's esti

Re: [PERFORM] Experience with HP Smart Array P400 and SATA drives?

On Wed, Dec 10, 2008 at 2:13 PM, Greg Smith <[EMAIL PROTECTED]> wrote: > 3ware 9650SE-4LPML is what I'd buy today if I wanted hardware SATA RAID. FWIW, I just put together a system with exactly that (4 320g drives in raid 10) and have been pleased with the results. I won't have any downtime to be

Re: [PERFORM] query plan with index having a btrim is different for strings of different length

BTW, if your queries typically constrain both lastname and firstname, it'd likely be worthwhile to make a 2-column index on lower(btrim(x_lastname)), lower(btrim(x_firstname)) regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgres

Re: [PERFORM] Experience with HP Smart Array P400 and SATA drives?

On Wed, Dec 10, 2008 at 12:13 PM, Greg Smith <[EMAIL PROTECTED]> wrote: > On Wed, 10 Dec 2008, Matthew Wakeling wrote: > >> I'd be interested in recommendations for RAID cards for small SATA >> systems. It's not anything to do with Postgres - I'm just intending to set >> up a little four-drive arra

Re: [PERFORM] query plan with index having a btrim is different for strings of different length

> You guys are right. I tried "Miller" and gave me the same result. Is there > any way to tune this so that for the common last names, the query run time > doesn't jump from <1s to >300s? > Thanks for the help! Can you send the output of EXPLAIN ANALYZE for both cases? ...Robert -- Sent via p

Re: [PERFORM] query plan with index having a btrim is different for strings of different length

Richard Yen <[EMAIL PROTECTED]> writes: > You guys are right. I tried "Miller" and gave me the same result. Is > there any way to tune this so that for the common last names, the > query run time doesn't jump from <1s to >300s? If the planner's estimation is that far off then there must be s

Re: [PERFORM] query plan with index having a btrim is different for strings of different length

On Dec 9, 2008, at 3:27 PM, Tom Lane wrote: Richard Yen <[EMAIL PROTECTED]> writes: I've discovered a peculiarity with using btrim in an index and was wondering if anyone has any input. What PG version is this? This is running on 8.3.3 In particular, I'm wondering if it's one of the early

Re: [PERFORM] Experience with HP Smart Array P400 and SATA drives?

On Wed, 10 Dec 2008, Matthew Wakeling wrote: I'd be interested in recommendations for RAID cards for small SATA systems. It's not anything to do with Postgres - I'm just intending to set up a little four-drive array for my home computer, with cheap 1TB SATA drives. Then why are you thinking o

Re: [PERFORM] Experience with HP Smart Array P400 and SATA drives?

On Wed, 10 Dec 2008, Scott Marlowe wrote: Or, if you don't have time to mess with it, just order an escalade or areca card and be done with it. :) I'd be interested in recommendations for RAID cards for small SATA systems. It's not anything to do with Postgres - I'm just intending to set up a

Re: [PERFORM] Experience with HP Smart Array P400 and SATA drives?

On Wed, Dec 10, 2008 at 9:05 AM, Mario Weilguni <[EMAIL PROTECTED]> wrote: > In fact, for this system we're currently going to RAID10, I'm convinced now. > With other systems we have, RAID5 is a safe option for one reason, the > machines are clusters, so we have (sort of) RAID50 here: > Machine A/R

Re: [PERFORM] Experience with HP Smart Array P400 and SATA drives?

Aidan Van Dyk schrieb: * Mario Weilguni <[EMAIL PROTECTED]> [081210 07:31]: Why not? I know it's not performing as good as RAID-10, but it does not waste 50% diskspace. RAID-6 is no option, because the performance is even worse. And, on another system with RAID-5 + spare and SAS drives,

Re: [PERFORM] Experience with HP Smart Array P400 and SATA drives?

* Mario Weilguni <[EMAIL PROTECTED]> [081210 07:31]: > Why not? I know it's not performing as good as RAID-10, but it does not > waste 50% diskspace. RAID-6 is no option, because the performance is > even worse. And, on another system with RAID-5 + spare and SAS drives, > the same controller

Re: [PERFORM] Experience with HP Smart Array P400 and SATA drives?

Me, I'd *never) allow RAID-5 for data I cared abut. Among other references, see (sorry for top-posting -- challenged reader and no other content to add) Greg Williamson Senior DBA DigitalGlobe Confidentiality Notice: This e-mail message,

Re: [PERFORM] Experience with HP Smart Array P400 and SATA drives?

Scott Marlowe schrieb: On Wed, Dec 10, 2008 at 12:45 AM, Mario Weilguni <[EMAIL PROTECTED]> wrote: A customer of us uses the P400 on a different machine, 8 SAS drives (Raid 5 as well), and the performance is very, very good. So we thought it's a good choice. Maybe the SATA drives are the root

Re: [PERFORM] Experience with HP Smart Array P400 and SATA drives?

On Wed, Dec 10, 2008 at 12:45 AM, Mario Weilguni <[EMAIL PROTECTED]> wrote: > > A customer of us uses the P400 on a different machine, 8 SAS drives (Raid 5 > as well), and the performance is very, very good. So we thought it's a good > choice. Maybe the SATA drives are the root of this problem? Wh

Re: [PERFORM] Need help with 8.4 Performance Testing

On Tue, 9 Dec 2008, Scott Carey wrote: For what it is worth, you can roughly double to triple the iops of an Intel X-25M on pure random reads if you queue up multiple concurrent reads rather than serialize them.  But it is not due to spindles, it is due to the latency of the SATA interface and

Re: [PERFORM] Experience with HP Smart Array P400 and SATA drives?

On Wed, Dec 10, 2008 at 5:29 AM, Mario Weilguni <[EMAIL PROTECTED]> wrote: > Aidan Van Dyk schrieb: >> >> * Joshua D. Drake <[EMAIL PROTECTED]> [081209 11:01]: >> >>> >>> Yes the SmartArray series is quite common and actually know to perform >>> reasonably well, in RAID 10. You still appear to be t

Re: [PERFORM] Experience with HP Smart Array P400 and SATA drives?

Aidan Van Dyk schrieb: * Joshua D. Drake <[EMAIL PROTECTED]> [081209 11:01]: Yes the SmartArray series is quite common and actually know to perform reasonably well, in RAID 10. You still appear to be trying RAID 5. *boggle* Are people *still* using raid5? /me gives up! Why

Re: [PERFORM] Need help with 8.4 Performance Testing

I did some further tests, that alter some of my statements below. Mainly: * I can do select count(1) queries at closer to disk speeds than my older tests (on a different machine) indicated. I can get ~800MB/sec where the disks can do 1200MB/sec and other single process tasks can go 1100MB/sec.