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 1AA6C9DC99C for ; Mon, 26 Dec 2005 19:52:08 -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 38190-02 for ; Mon, 26 Dec 2005 19:52:10 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 06B559DC9F1 for ; Mon, 26 Dec 2005 19:52:05 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id jBQNq88f015619; Mon, 26 Dec 2005 18:52:08 -0500 (EST) To: David Scott cc: pgsql-performance@postgresql.org Subject: Re: Performance hit on large row counts In-reply-to: <43B077AB.1000309@apptechsys.com> References: <43B05AA6.9020902@apptechsys.com> <12356.1135632990@sss.pgh.pa.us> <43B077AB.1000309@apptechsys.com> Comments: In-reply-to David Scott message dated "Mon, 26 Dec 2005 15:07:23 -0800" Date: Mon, 26 Dec 2005 18:52:08 -0500 Message-ID: <15618.1135641128@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.047 required=5 tests=[AWL=0.047] X-Spam-Score: 0.047 X-Spam-Level: X-Archive-Number: 200512/627 X-Sequence-Number: 16448 David Scott writes: > I didn't mention I was the only user with transactions open on the > system during this. Would cluster eliminate more rows then vacuum full > if the only open transaction is the one running the vacuum and it is a > clean transaction? It wouldn't eliminate more rows, but it could nonetheless produce a smaller table. IIRC, VACUUM FULL stops shrinking as soon as it finds a row that there is no room for in lower-numbered table pages; so a large row near the end of the table could block squeezing-out of small amounts of free space in earlier pages of the table. I doubt this effect is significant most of the time, but in a table with widely varying row sizes it might be an issue. Also, CLUSTER can definitely produce smaller *indexes* than VACUUM FULL. VACUUM FULL operates at a serious disadvantage when it comes to indexes, because in order to move a tuple it has to actually make extra index entries. >> I'm not at all sure I believe your premise that querying for a different >> key value excludes cache effects, btw. On modern hardware it's likely >> that CLUSTER would leave the *whole* of these tables sitting in kernel >> disk cache. >> > You are exactly right. After rebooting the entire box and running > the query the query time was 15 seconds. Rebooting the box, running > cluster on all three tables and then executing the query was 120 ms. Is > calling cluster the only way to ensure that these tables get loaded into > cache? Running select * appeared to cache some but not all. Hm, I'd think that SELECT * or SELECT count(*) would cause all of a table to be cached. It wouldn't do anything about caching the indexes though, and that might explain your observations. regards, tom lane