Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 8EF6E9FB9D1 for ; Sat, 19 May 2007 14:58:59 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 23786-08 for ; Sat, 19 May 2007 14:58:54 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 3FF919FB378 for ; Sat, 19 May 2007 14:58:56 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l4JHwrWu014601; Sat, 19 May 2007 13:58:53 -0400 (EDT) To: Steve Holdoway cc: pgsql-admin@postgresql.org Subject: Re: improve select performance... In-reply-to: <20070519145145.0c16e6fc.steve.holdoway@firetrust.com> References: <20070516110810.ba3554fe.steve.holdoway@firetrust.com> <20070519145145.0c16e6fc.steve.holdoway@firetrust.com> Comments: In-reply-to Steve Holdoway message dated "Sat, 19 May 2007 14:51:45 +1200" Date: Sat, 19 May 2007 13:58:53 -0400 Message-ID: <14600.1179597533@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/204 X-Sequence-Number: 25524 Steve Holdoway writes: > I managed to get this working properly by dropping the index on the > url column, and making it the primary key instead. Can anyone point me > at docs that define the difference between a primary key and an unique > index on the same field? I'm a bit confused here! A primary key is a unique index plus a not-null constraint on its column(s), plus it's the default reference target for FOREIGN KEY references to the table (which is why there can be only one per table). AFAIK that's it. For the purposes of SELECTs there really is no difference. >> db=> \d badurls .. >> Indexes: >> "idxbadurls_url" hash (url) >> "idxbadurls_version" btree (version) One problem here is you used a hash index. Postgres' hash index implementation is pretty poor. I suspect that the explicit marking of the index as unique might've changed the plan too; it's fairly obvious from your EXPLAIN that the planner didn't previously know the column was unique. (Which suggests that you oughta ANALYZE more often.) regards, tom lane