Skip site navigation (1) Skip section navigation (2)

Not using Primary Key in query

From: Josh Sacks <josh(dot)sacks(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Not using Primary Key in query
Date: 2004-05-25 18:37:55
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
I can't understand what's going on in this simple query:

select 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.

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)
     ->  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

To prove that in index query is possible, I tried:
select 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?



pgsql-performance by date

Next:From: Vitaly BelmanDate: 2004-05-25 19:53:05
Subject: Re: PostgreSQL caching
Previous:From: Jochem van DietenDate: 2004-05-25 15:37:44
Subject: Re: PostgreSQL caching

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group