Re: [PERFORM] Help with tuning this query (with explain analyze finally)

2) Force PG to drop the merge join via SET ENABLE_MERGEJOIN = FALSE; Actually, it was 312 milliseconds, so it got worse. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

[PERFORM] name search query speed

I have about 5M names stored on my DB. Currently the searches are very quick unless, they are on a very common last name ie. SMITH. The Index is always used, but I still hit 10-20 seconds on a SMITH or Jones search, and I average about 6 searches a second and max out at about 30/s. Any suggestions

Re: [PERFORM] name search query speed

I'm not sure what the answer is but maybe I can help? Would clustering the name index make this faster? I thought that would bunch up the pages so the names were more or less in order, which would improve search time. Just a guess though. Ken - Original Message - From: Jeremiah Jahn

Re: [PERFORM] name search query speed

yes, it does. I forgot to mention, that I also have clustering on that table by my name_field index. My Bad. On Thu, 2005-03-03 at 12:00 -0500, Ken Egervari wrote: I'm not sure what the answer is but maybe I can help? Would clustering the name index make this faster? I thought that would

Re: [PERFORM] Help with tuning this query (with explain analyze finally)

Ken, Well, I'm a bit stumped on troubleshooting the actual query since Windows' poor time resolution makes it impossible to trust the actual execution times. Obviously this is something we need to look into for the Win32 port for 8.1 .. shared_buffers = 1000 This may be slowing up that

Re: [PERFORM] name search query speed

Jeremiah Jahn wrote: I have about 5M names stored on my DB. Currently the searches are very quick unless, they are on a very common last name ie. SMITH. The Index is always used, but I still hit 10-20 seconds on a SMITH or Jones search, and I average about 6 searches a second and max out at about

Re: [PERFORM] name search query speed

Jeremiah, I have about 5M names stored on my DB. Currently the searches are very quick unless, they are on a very common last name ie. SMITH. The Index is always used, but I still hit 10-20 seconds on a SMITH or Jones search, and I average about 6 searches a second and max out at about 30/s.

Re: [PERFORM] name search query speed

Hi, Jeremiah, Jeremiah Jahn schrieb: yes, it does. I forgot to mention, that I also have clustering on that table by my name_field index. My Bad. Fine. Did you run ANALYZE and CLUSTER on the table after every large bunch of insertions / updates? Markus -- Markus Schaber | Dipl.

Re: [PERFORM] name search query speed

On Thu, 2005-03-03 at 11:46 -0600, John A Meinel wrote: Jeremiah Jahn wrote: I have about 5M names stored on my DB. Currently the searches are very quick unless, they are on a very common last name ie. SMITH. The Index is always used, but I still hit 10-20 seconds on a SMITH or Jones

Re: [PERFORM] name search query speed

On Thu, 2005-03-03 at 09:44 -0800, Josh Berkus wrote: Jeremiah, I have about 5M names stored on my DB. Currently the searches are very quick unless, they are on a very common last name ie. SMITH. The Index is always used, but I still hit 10-20 seconds on a SMITH or Jones search, and I

Re: [PERFORM] name search query speed

-Original Message- From: Jeremiah Jahn [mailto:[EMAIL PROTECTED] Sent: Thursday, March 03, 2005 2:15 PM To: John A Meinel Cc: postgres performance Subject: Re: [PERFORM] name search query speed [...] So the count for this is generated on the fly needed for pagination in the app

Re: [PERFORM] name search query speed

doesn't that cause two queries? I used to do it that way and cut my time substantially by counting in-line. Even though the results were cached it still took more time. Also since the tables is constantly be updated the returned total would not always match the number of results on the second

Re: [PERFORM] name search query speed

Jeremiah Jahn wrote: On Thu, 2005-03-03 at 11:46 -0600, John A Meinel wrote: ... Not really, about 2% of the returned rows are thrown away for security reasons based on the current user, security groups they belong to and different flags in the data itself. So the count for this is generated on

Re: [PERFORM] name search query speed

Hi, Jeremiah, Jeremiah Jahn schrieb: doesn't that cause two queries? I used to do it that way and cut my time substantially by counting in-line. Even though the results were cached it still took more time. This sounds rather strange. Also since the tables is constantly be updated the

Re: [PERFORM] Help with tuning this query (with explain analyze finally)

Josh, I did everything you said and my query does perform a bit better. I've been getting speeds from 203 to 219 to 234 milliseconds now. I tried increasing the work mem and the effective cache size from the values you provided, but I didn't see any more improvement. I've tried to looking

Re: [PERFORM] Help with tuning this query (with explain analyze finally)

Ken Egervari wrote: Josh, I did everything you said and my query does perform a bit better. I've been getting speeds from 203 to 219 to 234 milliseconds now. I tried increasing the work mem and the effective cache size from the values you provided, but I didn't see any more improvement. I've

Re: [PERFORM] name search query speed

Jeremiah Jahn wrote: I have about 5M names stored on my DB. Currently the searches are very quick unless, they are on a very common last name ie. SMITH. The Index is always used, but I still hit 10-20 seconds on a SMITH or Jones search, and I average about 6 searches a second and max out at about

Re: [PERFORM] Help with tuning this query (with explain analyze finally)

Josh, Thanks so much for your comments. They are incredibly insightful and you clearly know your stuff. It's so great that I'm able to learn so much from you. I really appreciate it. Do you need the interior sort? It's taking ~93ms to get 7k rows from shipment_status, and then another 30ms