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

Re: Subquery WHERE IN or WHERE EXISTS faster?

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: "Ulrich" <ulrich(dot)mierendorff(at)gmx(dot)net>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Subquery WHERE IN or WHERE EXISTS faster?
Date: 2008-06-28 23:01:08
Message-ID: 87skuxp3kb.fsf@oxford.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-performance
"Ulrich" <ulrich(dot)mierendorff(at)gmx(dot)net> writes:

> EXPLAIN ANALYZE SELECT speed FROM processors WHERE id IN (SELECT processorid
> FROM users_processors WHERE userid=4040) ORDER BY speed ASC LIMIT 10 OFFSET 1;
>
> Limit  (cost=113.73..113.75 rows=7 width=5) (actual time=0.335..0.340 rows=10 loops=1)
>   ->  Sort  (cost=113.73..113.75 rows=8 width=5) (actual time=0.332..0.333 rows=11 loops=1)

                                                                                  ^^

>         Sort Key: processors.speed
>         Sort Method:  quicksort  Memory: 17kB
>         ->  Nested Loop  (cost=47.22..113.61 rows=8 width=5) (actual time=0.171..0.271 rows=13 loops=1)
>               ->  HashAggregate  (cost=47.22..47.30 rows=8 width=4) (actual time=0.148..0.154 rows=13 loops=1)
>                     ->  Bitmap Heap Scan on users_processors  (cost=4.36..47.19 rows=12 width=4) (actual time=0.074..0.117 rows=13 loops=1)

                                                                                                                                  ^^

>                                 Index Cond: (userid = 4040)
>               ->  Index Scan using processors_pkey on processors (cost=0.00..8.28 rows=1 width=9) (actual time=0.006..0.007 rows=1 loops=13)
>                     Index Cond: (processors.id = users_processors.processorid)


It looks to me like you have some processors which appear in
"users_processors" but not in "processors". I don't know your data model but
that sounds like broken referential integrity  to me.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!

In response to

Responses

pgsql-performance by date

Next:From: UlrichDate: 2008-06-29 10:15:25
Subject: Re: Subquery WHERE IN or WHERE EXISTS faster?
Previous:From: UlrichDate: 2008-06-28 22:07:32
Subject: Re: Subquery WHERE IN or WHERE EXISTS faster?

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