Thread: A question about plans and multi-key pks

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]

Steve Rogerson <steve.pg@yewtc.demon.co.uk> writes:
> 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?

The rowcount estimate is made by combining selectivities of the WHERE
conditions; what you're seeing is a consequence of not having very good
knowledge about cross-column correlations.  The actual details of the plan
(ie, that the pkey index can be used) are determined much later.

            regards, tom lane