X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 8BF409FB2D2 for ; Tue, 19 Sep 2006 16:03:55 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 49657-09 for ; Tue, 19 Sep 2006 16:03:46 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web37908.mail.mud.yahoo.com (web37908.mail.mud.yahoo.com [209.191.91.170]) by postgresql.org (Postfix) with SMTP id 3EED79FB2BC for ; Tue, 19 Sep 2006 15:59:16 -0300 (ADT) Received: (qmail 21544 invoked by uid 60001); 19 Sep 2006 18:59:13 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=LFIofD9B6nJTJGB5SV/Wojw7XMOqj8+p2rdGwiXFrxowcVrerPoNb92c6dpRRBIn367gibpGQVkTokteEITPOi2U94JxviyJ1AExwCYjR5d7/LsC2lrNKuex/fHAWU69MBOlq+Ws84CSHj8RVHpnU4q08Hre1wBg98oA881jeOE= ; Message-ID: <20060919185913.21542.qmail@web37908.mail.mud.yahoo.com> Received: from [216.173.173.66] by web37908.mail.mud.yahoo.com via HTTP; Tue, 19 Sep 2006 11:59:13 PDT Date: Tue, 19 Sep 2006 11:59:13 -0700 (PDT) From: CG Reply-To: CG Subject: Re: Nested loops are killing throughput To: Tom Lane Cc: pgsql-sql@postgresql.org In-Reply-To: <15195.1158689872@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.355 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS X-Spam-Level: *** X-Archive-Number: 200609/132 X-Sequence-Number: 26343 >If packet_status is large, that seems like a perfectly reasonable plan >to me. If not ... what data type is packet_uuid? Is its equality op >marked mergeable or hashable? It is of type uniqueidentifier ... CREATE OPERATOR =( PROCEDURE = uniqueidentifier_eq, LEFTARG = uniqueidentifier, RIGHTARG = uniqueidentifier, COMMUTATOR = =, RESTRICT = eqsel, JOIN = eqjoinsel, HASHES, MERGES, SORT1 = <, SORT2 = <, LTCMP = <, GTCMP = >); It looks like it is marked both mergable and hashable. So what size (I have no idea what units to use to think about table size... kilobytes? pages? rows?) is "large" ... It seems like we recently stepped over that imaginary line between medium and large. We could re-tune PostgreSQL... What settings would make sense to tweak? Sort Memory? We could partition the tables... I understand the theory behind table partitioning and partial indexes, but I've never come across a howto or even a best practices list. We could remove data from the table... I don't like this as much, but if that's the only way, that's the only way.