Re: improve select performance...

Steve Holdoway <steve(dot)holdoway(at)firetrust(dot)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