Re: [PERFORM] Delete performance again
OK, I did try you proposal and correlated subselect. I have a database ~90 companies. First try was to remove randomly selected 1000 companies Uncorrelated subselect: 65899ms Correlated subselect: 97467ms using: 9605ms my way: 104979ms. (without constraints recreate) My is the worst because it is oriented on massive delete. So I thought USING would perform better, so I did try 1 companies my way: 190527ms. (without constraints recreate) using: 694144ms I was a little shocked, but I did check plans and found out that it did switch from Nested Loop to Hash Join. I did disable Hash Join, it not show Merge Join. This was also disabled and I've got 747253ms. Then I've tried combinations: Without hash join it was the best result of 402629ms, without merge join it was 1096116ms. My conclusion: Until optimizer would take into account additional actions needed (like constraints check/cascade deletes/triggers), it can not make good plan.
Re: [PERFORM] Disc space usage
On Wed, 8 Oct 2008, Tom Lane wrote: One other bit of possibly useful data would be to eyeball the file mod times in the orphaned subdirectories. If they were from failed CREATE DATABASEs then I'd expect every file in a given directory to have the same mod time (modulo the amount of time it takes to copy the DB, which is probably not trivial for the DB sizes you're dealing with). Yes, I did that, and the file modification times were in such a pattern. If you could also correlate that to the times you saw CREATE failures then it'd be pretty convincing that we know failed CREATEs are the issue. Can't do that until next time it happens, because we don't have the logs from when it did happen any more. Matthew -- Jadzia: Don't forget the 34th rule of acquisition: Peace is good for business. Quark: That's the 35th. Jadzia: Oh yes, that's right. What's the 34th again? Quark: War is good for business. It's easy to get them mixed up. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] low performance on functions returning setof record
Hi there, I use different functions returning setof record, and they are working well. The problem is the performance when I use those functions in joins, for instance: SELECT * FROM Table1 t1 JOIN Function1( a1, a2, ... aN ) AS f1( ColA int4, ColB varchar, ... ) ON t1.ColX = f1.ColA The problem is I'm not able to make indexes on the function, even inside I have just another select statement from different permanent tables, with some where clauses depending on the function arguments. Do you know a way to build such a function, returning something I can join in an outer select statement like above, using indexes or another way to run it faster ? TIA, Sabin -- 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] low performance on functions returning setof record
Sabin Coanda [EMAIL PROTECTED] writes: I use different functions returning setof record, and they are working well. The problem is the performance when I use those functions in joins, for instance: SELECT * FROM Table1 t1 JOIN Function1( a1, a2, ... aN ) AS f1( ColA int4, ColB varchar, ... ) ON t1.ColX = f1.ColA The problem is I'm not able to make indexes on the function, even inside I have just another select statement from different permanent tables, with some where clauses depending on the function arguments. There's not a lot you can do about that at the moment. 8.4 will have the ability to inline functions returning sets, if they're SQL-language and consist of just a single SELECT, but existing releases won't do it. You might consider trying to refactor your stuff to use views ... regards, tom lane -- 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] low performance on functions returning setof record
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, Le 9 oct. 08 à 21:30, Tom Lane a écrit : There's not a lot you can do about that at the moment. 8.4 will have the ability to inline functions returning sets, if they're SQL- language and consist of just a single SELECT, but existing releases won't do it. I'm actually using 8.3 functions cost/rows planner estimation to trick it into avoiding nestloop into some INNER JOIN situations where any amount of up-to-date statistics won't help. Will the 8.4 ability to inline plain SQL still consider the given hardcoded ROWS estimation? FWIW the difference of timing of one of the queries where I'm using this trick is about 35 mins or more against 48 seconds. It allows the planner to choose MergeJoin paths instead of Nestloop ones, where inner loop has several millions records, and definitely not just several records, like planner/stats bet. Regards, - -- dim -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (Darwin) iEYEARECAAYFAkjuYYcACgkQlBXRlnbh1bmAjgCePkyl9qWTpQ1Gdk/yp3IINK+z g8EAoJuAzu9B3GUiPI1J5dCcbzeiSABG =5J6b -END PGP SIGNATURE- -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
