From: Ulrich <ulrich.mierendorff@gmx.net>
To: Gregory Stark <stark@enterprisedb.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Subquery WHERE IN or WHERE EXISTS faster?
Date: Sun, 29 Jun 2008 12:15:25 +0200
Message-ID: <486760BD.3010409@gmx.net> (raw)
In-Reply-To: <87skuxp3kb.fsf@oxford.xeocode.com>
References: <48665741.2060900@gmx.net>
<29009.1214668423@sss.pgh.pa.us>
<4866B624.1090906@gmx.net>
<87skuxp3kb.fsf@oxford.xeocode.com>
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@gmx.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.
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ulrich.mierendorff@gmx.net, stark@enterprisedb.com, tgl@sss.pgh.pa.us
Subject: Re: Subquery WHERE IN or WHERE EXISTS faster?
In-Reply-To: <486760BD.3010409@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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