X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 3141D9FB27E for ; Tue, 19 Sep 2006 15:05:17 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 03847-09 for ; Tue, 19 Sep 2006 18:05:08 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web37903.mail.mud.yahoo.com (web37903.mail.mud.yahoo.com [209.191.91.165]) by postgresql.org (Postfix) with SMTP id EF6469FB280 for ; Tue, 19 Sep 2006 15:05:07 -0300 (ADT) Received: (qmail 16878 invoked by uid 60001); 19 Sep 2006 18:05:06 -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=ZrlHmm8tWbTePDO9onnt6JFE1ihl8BNsiH+QbS0wciOOFoIEUQMLsA4UeIkqNq0wl1LhRgzdHMpTP49FrzLszNthOPi1IJLmZmjuAIiEaOwabBp/etzE8cGrvry4iDFispkyAGUhVvL2bipFg8jsNBAkivY/+ZdqHIJShInq4Ok= ; Message-ID: <20060919180506.16876.qmail@web37903.mail.mud.yahoo.com> Received: from [216.173.173.66] by web37903.mail.mud.yahoo.com via HTTP; Tue, 19 Sep 2006 11:05:06 PDT Date: Tue, 19 Sep 2006 11:05:06 -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: <4769.1158678187@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/130 X-Sequence-Number: 26341 ----- Original Message ---- From: Tom Lane To: CG Cc: pgsql-sql@postgresql.org Sent: Tuesday, September 19, 2006 11:03:07 AM Subject: Re: [SQL] Nested loops are killing throughput 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. n/p ... I'll ditch it. Even if I simplify that query down to a straight-forward example... select dp.*, dps.status from dpo.packet dp, dpo.packet_status dps where dp.packet_uuid = dps.packet_uuid and dp.username='joeuser'; I'm still getting the nested loop for a join. Nested Loop (cost=100000000.00..100013378.98 rows=2206 width=145) (actual time=46.743..18202.318 rows=2225 loops=1) -> Index Scan using packet_user_idx on packet dp (cost=0.00..88.03 rows=2206 width=125) (actual time=42.263..124.519 rows=2225 loops=1) Index Cond: ((username)::text = 'joeuser'::text) -> Index Scan using packet_status_puuid_pkey on packet_status dps (cost=0.00..6.01 rows=1 width=20) (actual time=8.115..8.117 rows=1 loops=2225) Index Cond: ("outer".packet_uuid = dps.packet_uuid) Total runtime: 18205.880 ms These indexes are being used, and the tables are freshly vacuum-analyzed... CREATE UNIQUE INDEX packet_puuid_idx ON dpo.packet USING btree (packet_uuid); CREATE INDEX packet_user_idx ON dpo.packet USING btree (username); CREATE UNIQUE INDEX packet_status_puuid_pkey ON dpo.packet_status USING btree (packet_uuid); I have no idea why I'm having such a difficult time digging through this data. It should be very straight-forward. CG