X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 47EC5D1B1B2 for ; Tue, 25 May 2004 15:38:22 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 60904-05 for ; Tue, 25 May 2004 15:38:03 -0300 (ADT) Received: from mproxy.gmail.com (mproxy.gmail.com [216.239.56.252]) by svr1.postgresql.org (Postfix) with SMTP id 4A476D1B49A for ; Tue, 25 May 2004 15:38:00 -0300 (ADT) Received: by mproxy.gmail.com with SMTP id r62so219755cwc for ; Tue, 25 May 2004 11:37:55 -0700 (PDT) Received: by 10.11.117.43 with SMTP id p43mr46934cwc; Tue, 25 May 2004 11:37:55 -0700 (PDT) Message-ID: Date: Tue, 25 May 2004 11:37:55 -0700 From: Josh Sacks To: pgsql-performance@postgresql.org Subject: Not using Primary Key in query Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/222 X-Sequence-Number: 7053 I can't understand what's going on in this simple query: select c.name from Candidate C where C.candidate_id in (select candidate_id from REFERRAL R where r.employee_id = 3000); Where Candidate.CANDIDATE_ID is the primary key for Candidate. Here's the EXPLAN ANALYZE: Seq Scan on candidate c (cost=100000000.00..100705078.06 rows=143282 width=18) (actual time=2320.01..2320.01 rows=0 loops=1) Filter: (subplan) SubPlan -> Materialize (cost=2.42..2.42 rows=3 width=4) (actual time=0.00..0.00 rows=0 loops=286563) -> Index Scan using referral_employee_id_index on referral r (cost=0.00..2.42 rows=3 width=4) (actual time=0.48..0.48 rows=0 loops=1) Index Cond: (employee_id = 3000) It seems to be accurately estimating the number of rows returned by the sub-query (3), but then it thinks that 143282 rows are going to be returned by the main query, even though we are querying based on the PRIMARY KEY! To prove that in index query is possible, I tried: select c.name from Candidate C where C.candidate_id in (99, 22, 23123, 2344) which resulted in: Index Scan using candidate_id_index, candidate_id_index, candidate_id_index, candidate_id_index on candidate c (cost=0.00..17.52 rows=4 width=18) (actual time=24.437..29.088 rows=3 loops=1) Index Cond: ((candidate_id = 99) OR (candidate_id = 22) OR (candidate_id = 23123) OR (candidate_id = 2344)) Any ideas what's causing the query planner to make such a simple and drastic error? Thanks, Josh