pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Steve Holdoway <steve.holdoway@firetrust.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: improve select performance...
Date: Sat, 19 May 2007 13:58:53 -0400
Message-ID: <14600.1179597533@sss.pgh.pa.us> (raw)
In-Reply-To: <20070519145145.0c16e6fc.steve.holdoway@firetrust.com>
References: <20070516110810.ba3554fe.steve.holdoway@firetrust.com>
	<20070519145145.0c16e6fc.steve.holdoway@firetrust.com>

Steve Holdoway <steve.holdoway@firetrust.com> 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



view thread (4+ messages)

Message-ID: <14600.1179597533@sss.pgh.pa.us>
Permalink:  ../14600.1179597533@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/14600.1179597533@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: tgl@sss.pgh.pa.us, steve.holdoway@firetrust.com
  Subject: Re: improve select performance...
  In-Reply-To: <14600.1179597533@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox