X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 552EC9DC84E for ; Fri, 23 Dec 2005 05:02:02 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 98594-09 for ; Fri, 23 Dec 2005 05:02:02 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from gw7.hlebprom.ru (unknown [62.148.250.13]) by postgresql.org (Postfix) with ESMTP id 52F739DC810 for ; Fri, 23 Dec 2005 05:01:58 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by gw7.hlebprom.ru (Postfix) with ESMTP id C835AAB175 for ; Fri, 23 Dec 2005 14:02:16 +0500 (YEKT) Received: from gw7.hlebprom.ru ([127.0.0.1]) by localhost (gw7.hlebprom.ru [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 29665-08 for ; Fri, 23 Dec 2005 14:02:15 +0500 (YEKT) Received: from [10.0.0.2] (unknown [212.57.174.2]) (using TLSv1 with cipher AES256-SHA (256/256 bits)) (No client certificate requested) by gw7.hlebprom.ru (Postfix) with ESMTP for ; Fri, 23 Dec 2005 14:02:15 +0500 (YEKT) Date: Fri, 23 Dec 2005 14:02:05 +0500 From: Anton Maksimenkov X-Mailer: The Bat! (v3.0) Professional Reply-To: Anton Maksimenkov X-Priority: 3 (Normal) Message-ID: <287988431.20051223140205@hlebprom.ru> To: pgsql-performance@postgresql.org Subject: DELETE, INSERT vs SELECT, UPDATE || INSERT MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hlebprom.ru X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.275 required=5 tests=[AWL=0.275] X-Spam-Score: 0.275 X-Spam-Level: X-Archive-Number: 200512/580 X-Sequence-Number: 16401 Hi, all. While working on algorithm of my project I came to question. Let it be table like this (user+cookie pair is the primary key). INT user INT cookie INT count Periodically (with period 10 minutes) this PostgreSQL table updated with my information. The main problem that some of pairs (user, cookie) may be already exists in PostgreSQL and must be updated, some not exists and must be inserted. My first way was to DELETE row with (user, cookie) pair which I'm going to update then INSERT new. This guarantees that there will not be an error when (user, cookie) pair already exists in table. And currently it works by this way. But I think that it lead to highly fragmentation of table and it need to be VACUUMED and ANALYZED far more frequently... Second idea was to try to SELECT (user, cookie) pair and then UPDATE it if it exists or INSERT if not. I has thought that if UPDATE will rewrite same place in file with new count it may lead to more compact table (file not grow and information about actual rows in file will not changed). And, if actual file blocks containing (user, cookie) pair will not be moved to new place in file, table need to be ANALYZED less frequently. But if UPDATE will actually insert new row in file, marking as 'free to use' previous block in file which was contain previous version of row, then again, table need to be VACUUMED and ANALYZED far more frequently... And this second idea will be completely waste of time and code. Because write on C code which "DELETE and INSERT" is more portably than "SELECT than UPDATE if there are rows, or INSERT if there are not". So, can anyone explain me is the actual mechanism of UPDATE can save resources and tables from been highly fragmented? Or it gives same results and problems and "DELETE then INSERT" is the best way? -- engineer