X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 6A6DDD1D5A3 for ; Fri, 2 Apr 2004 04:44:32 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 41032-07 for ; Fri, 2 Apr 2004 00:44:31 -0400 (AST) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id 9D003D1D2B5 for ; Fri, 2 Apr 2004 00:44:30 -0400 (AST) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id 094E617C4A8; Thu, 1 Apr 2004 23:44:30 -0500 (EST) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1B9GXd-0005Qq-00; Thu, 01 Apr 2004 23:44:29 -0500 To: "Damien Dougan" Cc: Subject: Re: Index Performance Help References: <004b01c3ebe1$7e0c9a70$7701a8c0@pestilence> In-Reply-To: <004b01c3ebe1$7e0c9a70$7701a8c0@pestilence> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 01 Apr 2004 23:44:29 -0500 Message-ID: <87d66rouoi.fsf@stark.xeocode.com> Lines: 25 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 postgresql.org X-Archive-Number: 200404/5 X-Sequence-Number: 6305 "Damien Dougan" writes: > Sample analyze output for an initial query: > > hydradb=# explain analyze select * from pvsubscriber where actorid = > 'b3432-asdas-232-Subscriber793500'; I take it pvsubscriber is a view? What's the definition of your view? > -> Index Scan using mc_actor_key on mc_actor > (cost=0.00..4.08 rows=1 width=69) > (actual time=39.497..39.499 rows=1 loops=1) Is this table regularly vacuumed? Is it possible it has lots of dead records with this value for actorid? Try running vacuum full, or better "vacuum full verbose" and keep the output, it might explain. What version of postgres is this? You might try reindexing all your indexes (but particularly this one). Older versions of postgres were prone to index bloat problems. -- greg