X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D3B1E329FEF for ; Wed, 6 Oct 2004 20:39:12 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 25337-04 for ; Wed, 6 Oct 2004 19:39:06 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id 7925D32A493 for ; Wed, 6 Oct 2004 20:38:58 +0100 (BST) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1CFHcm-0000xZ-00; Wed, 06 Oct 2004 15:38:56 -0400 To: Patrick Clery Cc: pgsql-performance@postgresql.org Subject: Re: Comparing user attributes with bitwise operators References: <200409160141.37604.patrick@phpforhire.com> <200410050039.24230.patrick@phpforhire.com> <200410050932.38595.josh@agliodbs.com> <200410061255.03052.patrick@phpforhire.com> In-Reply-To: <200410061255.03052.patrick@phpforhire.com> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 06 Oct 2004 15:38:56 -0400 Message-ID: <87y8ijy6un.fsf@stark.xeocode.com> Lines: 27 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/66 X-Sequence-Number: 8542 Patrick Clery writes: > PART OF THE QUERY PLAN: > Index Scan using people_attributes_pkey on people_attributes pa (cost=0.00..5.32 rows=1 width=20) > Index Cond: (pa.person_id = "outer".person_id) > Filter: (((ARRAY[age, gender, orientation, children, drinking, You'll probably have to show the rest of the plan for anyone to have much idea what's going on. It seems to be part of a join of some sort and the planner is choosing to drive the join from the wrong table. This may make it awkward to force the right plan using enable_seqscan or anything like that. But GiST indexes don't have very good selectivity estimates so I'm not sure you can hope for the optimizer to guess right on its own. > Is it all the foreign keys that are stalling the drop? I have done VACUUM > ANALYZE on the entire db. Could anyone offer some insight as to why this > index is not being used or why the index is not dropping easily? I don't think foreign keys cause problems dropping indexes. Foreign key constraints are just checked whenever there's an insert/update/delete. Perhaps you're just underestimating the size of this index and the amount of time it'll take to delete it? Or are there queries actively executing using the index while you're trying to delete it? Or a vacuum running? -- greg