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 4AED89F931A for ; Tue, 19 Sep 2006 12:03:18 -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 75843-03 for ; Tue, 19 Sep 2006 12:03:08 -0300 (ADT) 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 EA8AF9FB1C6 for ; Tue, 19 Sep 2006 12:03:08 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k8JF37pv004770; Tue, 19 Sep 2006 11:03:07 -0400 (EDT) To: CG cc: pgsql-sql@postgresql.org Subject: Re: Nested loops are killing throughput In-reply-to: <20060919144628.32833.qmail@web37903.mail.mud.yahoo.com> References: <20060919144628.32833.qmail@web37903.mail.mud.yahoo.com> Comments: In-reply-to CG message dated "Tue, 19 Sep 2006 07:46:28 -0700" Date: Tue, 19 Sep 2006 11:03:07 -0400 Message-ID: <4769.1158678187@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.102 tagged_above=0 required=5 tests=AWL, SPF_HELO_PASS, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/125 X-Sequence-Number: 26336 CG writes: > -> Nested Loop (cost=0.00..744.28 rows=1 width=16) (actual time=31.227..6980.765 rows=5436 loops=1) > -> Nested Loop (cost=0.00..135.29 rows=101 width=16) (actual time=25.514..273.660 rows=5436 loops=1) > ... > -> Index Scan using packet_status_puuid_pkey on packet_status dps (cost=0.00..6.02 rows=1 width=16) (actual time=1.226..1.228 rows=1 loops=5436) > Index Cond: (dps.packet_uuid = "outer".packet_uuid) > Filter: ((status & 2) = 0) One problem you've got is that the planner has no stats about the selectivity of that status condition. My advice would be to forget the cute bitmask and store one or more plain boolean columns. Easier to write queries against and way more tractable statistically. regards, tom lane