X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 519D431082B for ; Mon, 30 Jun 2003 19:05:38 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 60659-01 for ; Mon, 30 Jun 2003 16:05:27 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 35A5B30FCE1 for ; Mon, 30 Jun 2003 16:05:27 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h5UJ5QQQ016645; Mon, 30 Jun 2003 15:05:26 -0400 (EDT) To: "Robert Wille" Cc: pgsql-performance@postgresql.org Subject: Re: Query planner plans very inefficient plans In-reply-to: <032101c33f31$0d239910$2264a8c0@zucchini> References: <032101c33f31$0d239910$2264a8c0@zucchini> Comments: In-reply-to "Robert Wille" message dated "Mon, 30 Jun 2003 11:57:20 -0600" Date: Mon, 30 Jun 2003 15:05:26 -0400 Message-ID: <16644.1056999926@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200306/208 X-Sequence-Number: 2292 "Robert Wille" writes: > select * from image natural join ancestry where ancestorid=1000000 and > (state & 7::bigint) = 0::bigint; The planner is not going to have any statistics that allow it to predict the number of rows satisfying that &-condition, and so it's unsurprising if its off-the-cuff guess has little to do with reality. I'd recommend skipping any cute tricks with bit-packing, and storing the state (and any other values you query frequently) as its own column, so that the query looks like select * from image natural join ancestry where ancestorid=1000000 and state = 0; ANALYZE should be able to do a reasonable job with a column that has 8 or fewer distinct values ... regards, tom lane