Re: [PERFORM] slow DELETE on 12 M row table

Greg Stark wrote: "waiting" means it's blocked trying to acquire a lock. Some open transaction has the table you're trying to index locked. Look in pg_locks and pg_stat_activity to find out who. Or you might find CREATE INDEX CONCURRENTLY fits your situation. http://www.postgresql.org/docs/8.3

Re: [PERFORM] slow DELETE on 12 M row table

On Fri, Jun 26, 2009 at 3:33 AM, Janet Jacobsen wrote: > (1) is my interpretation of the posts correct, i.e., if I am deleting > rows from > table1, where the pkey of table 1 is a fkey in table 2, then do I need > to create an > index on the fkey field in table 2? Exactly right. The index on the t

[PERFORM] slow DELETE on 12 M row table

Hi. We are running Postgres 8.3.7 on an eight-processor Linux system. Because the machine has very little local disk, the database files are on a file system running GPFS. The machine is mostly dedicated to processing images. After the images are processed, the image attributes and processing pa

Re: [PERFORM] Nested Loop "Killer" on 8.1

On Thu, Jun 25, 2009 at 10:05 PM, Tom Lane wrote: > > Uh, it appears to me the string *does* contain _ characters; perhaps the > OP has neglected to escape those? Sigh. Indeed. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.o

Re: [PERFORM] Nested Loop "Killer" on 8.1

Greg Stark writes: > On Wed, Jun 24, 2009 at 1:43 PM, Dave North wrote: > Why use "like" for a constant string with no % or _ characters? If you > used = the planner might be able to come up with a better estimate. Uh, it appears to me the string *does* contain _ characters; perhaps the OP has ne

Re: [PERFORM] Nested Loop "Killer" on 8.1

On 06/25/2009 04:36 PM, Greg Stark wrote: AND web_user_property_directory_outbox.prop_key like 'location_node_directory_outbox' Why use "like" for a constant string with no % or _ characters? If you used = the planner might be able to come up with a better estimate Any reason why "l

Re: [PERFORM] Nested Loop "Killer" on 8.1

On Wed, Jun 24, 2009 at 1:43 PM, Dave North wrote: > Essentially, we're seeing a query plan that is taking 95 secs with a nested > loop execution plan and 1 sec with a merge join plan.  We've tried > increasing the default_statistics_target to 1000 and re-analyzed but the > same query plan is retu

Re: [PERFORM] Nested Loop "Killer" on 8.1

Dave, Is there further optimizations we can do to change the plan? Is this perhaps addressed in a later release? Given the left joins, a later release might help; I know we did a lot to improve left join plans in 8.3. It would be worth testing if you can test an upgrade easily. -- Josh Be

Re: [PERFORM] Implications of having large number of users

I'd be glad to hear any opinions/suggestions. Many thanks to everyone who responded! Mike -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] tsvector_update_trigger performance?

Also consider on update triggers that you could want to run anyway -- dim Le 25 juin 2009 à 07:45, Craig Ringer a écrit : On Wed, 2009-06-24 at 21:03 -0700, Chris St Denis wrote: This sounds like something that should just be on by default, not a trigger. Is there some reason it would