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
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
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
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 bun
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 mer
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
3
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
> 3
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. Informatik
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
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
> -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
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 query
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 th
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 r
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 in
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
tri
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
3
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 to
18 matches
Mail list logo