Re: [PERFORM] Slow deleting tables with foreign keys

still in the development and tuning process, so I will do some analysis of the index stats to see if they are indeed redundant. Cheers, Jeremy From: Bob Lunney [bob_lun...@yahoo.com] Sent: Friday, 1 April 2011 3:54 a.m. To: pgsql-performance@postgresql.org;

[PERFORM] Slow deleting tables with foreign keys

Hi All, I'm trying to delete one row from a table and it's taking an extremely long time. This parent table is referenced by other table's foreign keys, but the particular row I'm trying to delete is not referenced any other rows in the associative tables. This table has the following structure

[PERFORM] Does exclusive locking improve performance?

In normal circumstances does locking a table in access exclusive mode improve insert, update and delete operation performance on that table. Is MVCC disabled or somehow has less work to do? Cheers Jeremy ___

Re: [PERFORM] Possible to improve query plan?

Thanks heaps for the advice. I will do some benchmarks to see how long it takes to cluster all of the database tables. Cheers, Jeremy -Original Message- From: Kevin Grittner [mailto:kevin.gritt...@wicourts.gov] Sent: Tuesday, 25 January 2011 1:02 p.m. To: Jeremy Palmer; Tom Lane Cc

Re: [PERFORM] Possible to improve query plan?

Might be a chance on 9.0 in a couple of weeks, when I do an upgrade on one our dev boxes. Kevin I've now clustered the table. And the performance did increase quite a bit. My only question is how often will I need to re-cluster the table, because it comes at quite a cost. The setup I'm running

Re: [PERFORM] Possible to improve query plan?

Thanks that seems to make the query 10-15% faster :) Cheers jeremy -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tuesday, 18 January 2011 9:24 a.m. To: Jeremy Palmer Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Possible to improve query plan

Re: [PERFORM] Possible to improve query plan?

-Original Message- From: Ing. Marcos Ortiz Valmaseda [mailto:mlor...@uci.cu] Sent: Tuesday, 18 January 2011 2:38 a.m. To: Jeremy Palmer Cc: pgsql-performance@postgresql.org; Andy Colson Subject: Re: [PERFORM] Possible to improve query plan? Which is the type of your application? You can see it on

Re: [PERFORM] Possible to improve query plan?

ere's the new plan with work_mem = 50mb: http://explain.depesz.com/s/xwv And here another plan with work_mem = 500mb: http://explain.depesz.com/s/VmO Thanks, Jeremy -Original Message- From: Andy Colson [mailto:a...@squeakycode.net] Sent: Monday, 17 January 2011 5:57 p.m. To: Jeremy

[PERFORM] Possible to improve query plan?

rom: Andy Colson [mailto:a...@squeakycode.net] Sent: Monday, 17 January 2011 5:22 p.m. To: Jeremy Palmer Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Possible to improve query plan? First, wow, those are long names... I had a hard time keeping track. Second: you have lots of dup

[PERFORM] Possible to improve query plan?

Hi all, I've come to a dead end in trying to get a commonly used query to perform better. The query is against one table with 10 million rows. This table has been analysed. The table definition is: CREATE TABLE version_crs_coordinate_revision ( _revision_created integer NOT NULL, _revision_

Re: [PERFORM] Index usage with functions in where condition

is a question for the PostGIS guys and a quick test could tell me anyway! My memory is that the GIST r-tree index is slow for points at the moment, and that a good implementation of a kd-tree index over GIST is required for better speed. Regards, Jeremy Palmer Geodetic Surveyor National Geo

[PERFORM] Index usage with functions in where condition

I'm having trouble getting the query planner to use indexes. The situation occurs when writing a query that uses functions for defining the parameters for the conditions on the indexed columns. The system I'm running is Windows Server 2003, using version 8.4.2 of PostgreSQL. This is the followi