Re: Two fast queries get slow when combined

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