Re: Subquery WHERE IN or WHERE EXISTS faster?

Hi,
Yes that looks strange. But it is not possible that I have processors in 
"users_processors" which do not appear in "processors", because 
"users_processors" contains foreign keys to "processors".

If I remove the LIMIT 10 OFFSET 1 the line "Sort (cost=.... rows=11.." 
disappears and the query return 13 correct processors from "processors". 
Then, I have tested different values for OFFSET. If I set Offset to "2" 
and LIMIT=10 the line is:
       Sort  (cost=113.73..113.75 rows=8 width=5) (actual 
time=0.322..0.330 rows=12 loops=1)
If I set Offset to "3" and LIMIT=10 it is
       Sort  (cost=113.73..113.75 rows=8 width=5) (actual 
time=0.321..0.328 rows=13 loops=1)

It looks like if this "row" is something like min(max_rows=13, 
LIMIT+OFFSET). But I do not completely understand the Syntax... ;-)

Kind regards
Ulrich

Gregory Stark wrote:
> "Ulrich" <ulrich.mierendorff@[EMAIL PROTECTED]
> 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.
>
>   


-- 
Sent via pgsql-performance mailing list (pgsql-performance@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance