A question about plans and multi-key pks

From: Steve Rogerson <steve(dot)pg(at)yewtc(dot)demon(dot)co(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: A question about plans and multi-key pks
Date: 2015-05-01 15:53:26
Message-ID: 5543A176.1090804@yewtc.demon.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

We have a table:

sjr_local1db=# \d cons_refs
Table "public.cons_refs"
Column | Type | Modifiers

-----------+------------------------+----------------------------------
code | bigint | not null
...
ref_type | character varying(20) | not null
ref_text | character varying(128) | not null
Indexes:
"cons_refs_pk" PRIMARY KEY, btree (code, ref_type)
"cons_refs_ref_type_ref_text_in" btree (ref_type, lower(ref_text::text))

I do a select :

ricodb=> explain analyze select * from cons_refs where ref_type = 'some_ref'
AND cons_code = 12345678;
QUERY PLAN

-----------------------------------------------------------------------------
Index Scan using cons_refs_pk on cons_refs (cost=0.00..37.89 rows=67
width=68) (actual time=0.020..0.021 rows=1 loops=1)
Index Cond: ((code = 12345678) AND ((ref_type)::text = 'some_ref'::text))
Total runtime: 0.036 ms
(3 rows)

Why does the plan come up with 67 rows, when by definition there can be at
most one as we are searching on the pk?

[pg 9.0.7 on RHEL 5.7]

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2015-05-01 16:43:17 Re: A question about plans and multi-key pks
Previous Message Murphy, Kevin 2015-05-01 15:26:37 Re: pg_dump permssion denied problem