DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender;
	b=dc0RkhLmwdRis5ogrSgtHQArRrg39ydv4ywciVXXYw7/ztSZRDdDN5/52NOG2g/Q6lHNZFW/bANyskwPW6tzz3DUrTiJzBN8hEYZBlAVdrHdBJeY/ryxh/z+OH8uuQXIn2uwvg4go0sFEEXu4exrOdkrnnxTavCq8Vb7Ro9IKZo=
Message-ID: <4727A645.2040302@enterprisedb.com>
Date: Tue, 30 Oct 2007 21:46:45 +0000
From: Heikki Linnakangas <heikki@enterprisedb.com>
User-Agent: Icedove 1.5.0.12 (X11/20070730)
MIME-Version: 1.0
To: cluster <skrald@amossen.dk>
CC: pgsql-performance@postgresql.org
Subject: Re: Two fast queries get slow when combined
References: <fg85ae$1740$1@news.hub.org>
In-Reply-To: <fg85ae$1740$1@news.hub.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

cluster wrote:
>   SELECT keyId, sortNum, count(1)
>   FROM stats s, items i
>   WHERE s.keyId = i.keyId AND i.sortNum > 123
>   GROUP BY i.keyId, i.sortNum
>   ORDER BY i.sortNum
>   LIMIT 50
> 
> Limit  (cost=3281.72..3281.84 rows=50 width=16) (actual 
> time=435.838..436.043 rows=50 loops=1)
>    InitPlan
>      ->  Limit  (cost=0.00..0.01 rows=1 width=0) (actual 
> time=0.016..0.021 rows=1 loops=1)
>            ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual 
> time=0.012..0.013 rows=1 loops=1)
>    ->  Sort  (cost=3281.71..3289.97 rows=3304 width=16) (actual 
> time=435.833..435.897 rows=50 loops=1)
>          Sort Key: i.sortNum
>          ->  Hash Join  (cost=2745.80..3088.59 rows=3304 width=16) 
> (actual time=364.247..413.164 rows=8490 loops=1)
>                Hash Cond: (s.keyId = i.keyId)
>                ->  HashAggregate  (cost=2270.53..2394.43 rows=9912 
> width=8) (actual time=337.869..356.533 rows=9911 loops=1)
>                      ->  Seq Scan on items  (cost=0.00..1527.16 
> rows=99116 width=8) (actual time=0.016..148.118 rows=99116 loops=1)
>                ->  Hash  (cost=408.47..408.47 rows=5344 width=12) 
> (actual time=26.342..26.342 rows=4491 loops=1)
>                      ->  Bitmap Heap Scan on items i 
> (cost=121.67..408.47 rows=5344 width=12) (actual time=5.007..16.898 
> rows=4491 loops=1)
>                            Recheck Cond: (sortNum >= $0)
>                            ->  Bitmap Index Scan on items_sortNum 
> (cost=0.00..120.33 rows=5344 width=0) (actual time=4.273..4.273 
> rows=13375 loops=1)
>                                  Index Cond: (sortNum >= $0)
> Total runtime: 436.421 ms
> (16 rows)

There's something odd about that plan. It's doing both a seq scan and a 
bitmap scan on "items", but I can't see stats table being mentioned 
anywhere. Looking at the row count, I believe that seq scan is actually 
on the stats table, not items like it says above. Is that really a 
verbatim copy of the output you got?

Which version of Postgres is this?

You could try rewriting the query like this:

SELECT keyId, sortNum,
   (SELECT count(*) FROM stats s WHERE s.keyId = i.keyId) AS stats_cnt
FROM items i
WHERE i.sortNum > 123
ORDER BY sortNum
LIMIT 50

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com