Re: [PERFORM] Nested loop Query performance on PK
Hello, Le 26/07/09 7:09, Greg Caulton a écrit : On Sun, Jul 26, 2009 at 1:02 AM, Greg Caulton caulton...@gmail.com mailto:caulton...@gmail.com wrote: Hello, It seems to me that the following query should be a lot faster. This runs in 17 seconds (regardless how many times I run it) select ac.* from application_controls_view ac, refs r where ac.custom_controller_ref_id = r.ref_id and r.ref_key like '%XYZ%'; [...] Does this seem right to you? Anything I can tune ? [...] Oh it seems to be the join that is throwing it off, because this runs in 600 ms select ac.* from application_controls_view ac where ac.application_control_id in ( 5745, 5760, [...] 50021066, 50020808 ) never mind, makes sense now - its fixed [...] The following rewritten query may be satisfiable for the generic case of using arbitrary LIKE pattern for refs.ref_key and performing in a short acceptable time as well: SELECT ac.* FROM application_controls_view AS ac INNER JOIN ( SELECT ref_id FROM refs WHERE ref_key LIKE '%XYZ%' ) AS r ON ac.custom_controller_ref_id = r.ref_id; The hint is to build a subquery, from refs table, and to move in the WHERE clause that only refers to refs column (ref_key here). This subquery results in a shorter table than the original (refs here), thence reducing the number of joins to perform with ac (no matter working with view or original table). Regards. -- nha / Lyon / France. -- 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] hyperthreaded cpu still an issue in 8.4?
On Tue, 21 Jul 2009, Doug Hunley wrote: Just wondering is the issue referenced in http://archives.postgresql.org/pgsql-performance/2005-11/msg00415.php is still present in 8.4 or if some tunable (or other) made the use of hyperthreading a non-issue. We're looking to upgrade our servers soon for performance reasons and am trying to determine if more cpus (no HT) or less cpus (with HT) are the way to go. If you're talking about the hyperthreading in the latest Intel Nehalem processors, I've been seeing great PostgreSQL performance from those. The kind of weird behavior the old generation hyperthreading designs had seems gone. You can see at http://archives.postgresql.org/message-id/alpine.gso.2.01.0907222158050.16...@westnet.com that I've cleared 90K TPS on a 16 core system (2 quad-core hyperthreaded processors) running a small test using lots of parallel SELECTs. That would not be possible if there were HT spinlock problems still around. There have been both PostgreSQL scaling improvments and hardware improvements since the 2005 messages you saw there that have combined to clear up the issues there. While true cores would still be better if everything else were equal, it rarely is, and I wouldn't hestitate to jump on Intel's bandwagon right now. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] Performance of quer or procedure going down when we are taking the backup
On Mon, Jul 20, 2009 at 6:15 AM, ramasubramanianramasubramania...@renaissance-it.com wrote: Dear all, Performance of query or procedure going down when we are taking the backup of that schema(it is obvious), But how to increase the performance. Regards, Ram. You're going to need to provide an awful lot more details than this if you want to have much chance of getting a useful answer, I think. At a high level, you want to find out which part your system is the bottleneck and then look for ways to remove the bottleneck. ...Robert -- 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] [BUGS] Postgres user authentification or LDAP authentification
Lauris Ulmanis wrote: Hello! I posted you a message about slowness of creation users more than 500 000 (#4919). It seems there is no workaround of this problem because of using pg_auth flat file. To override this problem is it possible to use LDAP authentification metod to identify each user and speed up system? No. The users still need to exist in the PG auth system. I'm sure this is just some missing optimization. Feel free to work on the code to improve performance for these cases. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
