pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Not using Primary Key in query
2+ messages / 2 participants
[nested] [flat]

* Not using Primary Key in query
@ 2004-05-25 18:37 Josh Sacks <josh.sacks@gmail.com>
  2004-05-29 05:06 ` Re: Not using Primary Key in query Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Josh Sacks @ 2004-05-25 18:37 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Not using Primary Key in query
  2004-05-25 18:37 Not using Primary Key in query Josh Sacks <josh.sacks@gmail.com>
@ 2004-05-29 05:06 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2004-05-29 05:06 UTC (permalink / raw)
  To: Josh Sacks <josh.sacks@gmail.com>; +Cc: pgsql-performance

Josh Sacks <josh.sacks@gmail.com> writes:
> I can't understand what's going on in this simple query:

If you are using anything older than PG 7.4, you should not expect good
performance from WHERE ... IN (sub-SELECT) queries.  There's essentially
no optimization happening there.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2004-05-29 05:06 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-25 18:37 Not using Primary Key in query Josh Sacks <josh.sacks@gmail.com>
2004-05-29 05:06 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox