'splain a postgresql EXPLAIN to me

Got this here query:

EXPLAIN
SELECT persons.id AS id, ppm.first
FROM myschema.persons
INNER JOIN myotherschema.ppm ON persons.key = ppm.pid
WHERE persons.id = 279759;

The column ppm.pid is a primary key AND in the index:

CREATE INDEX ppm_pkey_index
  ON myotherschema.ppm
  USING btree
  (pid);

And so here's the EXPLAIN:

Hash Join  (cost=8.31..3105.40 rows=306 width=23)
  Hash Cond: (textin(int4out(ppm.pid)) = persons.key)
  ->  Seq Scan on ppm  (cost=0.00..2711.33 rows=61233 width=23)
  ->  Hash  (cost=8.29..8.29 rows=1 width=12)
        ->  Index Scan using pskey on persons  (cost=0.00..8.29 rows=1 width=12)
              Index Cond: (id = 279759)

It doesn't seem to be using the ppm_pkey_index at all: it still seems to be scanning 61,233 rows. Why is this? Am I misreading it? Corollary: aren't primary keys automatically indexed in postgresql? Is开发者_运维百科 my index then redundant?

Primary keys create UNIQUE INDEXES on your key. So your index is redundant indeed.

Did you run vacuum analyze on your table after creating the index?

sql> vacuum analyze myotherschema.ppm;

I see another problem now: are ppm.pid and persons.key of the same field type? You may run into perfomance issues due to unnecessary data conversions, and inability to use indexes because you are not indexing on casting functions you need to use on join...

What happens if you change it to:

EXPLAIN
SELECT persons.id AS id, ppm.first
FROM myschema.persons
INNER JOIN myotherschema.ppm ON persons.id = 279759
AND persons.key = ppm.pid;