X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 89F56D1B536 for ; Mon, 17 Nov 2003 14:51:58 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 81735-07 for ; Mon, 17 Nov 2003 10:51:31 -0400 (AST) Received: from jacobson.be (c20753a.mael.bostream.se [217.215.37.134]) by svr1.postgresql.org (Postfix) with SMTP id 70E78D1B50C for ; Mon, 17 Nov 2003 10:51:26 -0400 (AST) Received: (qmail 2433 invoked by uid 99); 17 Nov 2003 14:51:21 -0000 Received: from dhcp-5.localdomain (dhcp-5.localdomain [192.168.0.5]) by mail.jacobson.be (IMP) with HTTP for ; Mon, 17 Nov 2003 15:51:20 +0100 Message-ID: <1069080680.3fb8e068eb941@mail.jacobson.be> Date: Mon, 17 Nov 2003 15:51:20 +0100 From: Joel Jacobson To: pgsql-performance@postgresql.org Subject: Backup/restore of pg_statistics MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit User-Agent: Internet Messaging Program (IMP) 3.2.2 X-Originating-IP: 192.168.0.5 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/135 X-Sequence-Number: 4695 Hi, I understand that it is not possible to occasionally re-plan the queries in a PL/pgSQL function without dropping and re-creating the function. I think it would be useful if the queries in a PL/pgSQL function could be re-planned on-the-fly. When a lot of data has been added/modified and ANALYZE is suitable to run, it would also be a great idea to re-plan the queries used in PL/pgSQL functions. I understand that this is not possible? The only way would be to DROP/CREATE the functions or to use EXECUTE. I don't think EXECUTE is an option, because preparing the queries every time the function is called is in my case not necessary and just a waste of performance. As a work-around, I am forced to, 1. populate the database with a lot of test data, 2. run ANALYZE, 3. and finally, create the PL/pgSQL functions The prepared queries in the functions will now be sufficiently optimized. I don't think this is a nice solution. I also thought of a slightly better solution, but I don't know if it is possible. My idea was to populate the database once and then save the data in pg_statistics generated by ANALYZE to a file. Every time the database needs to be created, the statistics could then be restored thus making the planner produce "future-optimized" queries when the PL/pgSQL functions are created, even though the database is empty. I would greatly appreciate any help/comments. Thank you. Joel Jacobson