Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id AC380633FA3 for ; Tue, 23 Feb 2010 08:18:31 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 16920-04 for ; Tue, 23 Feb 2010 12:18:14 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from ey-out-2122.google.com (ey-out-2122.google.com [74.125.78.25]) by mail.postgresql.org (Postfix) with ESMTP id CD7AC633498 for ; Tue, 23 Feb 2010 08:18:20 -0400 (AST) Received: by ey-out-2122.google.com with SMTP id 25so714999eya.33 for ; Tue, 23 Feb 2010 04:18:18 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from :user-agent:mime-version:to:cc:subject:references:in-reply-to :content-type:content-transfer-encoding; bh=Z86tALpuz3gkJu5KzRZ+EXzCDJ6xZ53mqyO4Qa/nhiU=; b=n2TjDDZCvRJSoxWgEdEWfI7AYLqBgn1Up6KcKs6TWuf/ajrFOovhKE0vogkCszOCfd slPwRqL5lxLtRNvAM0lXr2XzABp4YT67C44aJKRsMQrP48bAytSYkyQNNoy2ezfcnUd8 xQuf/Dkn27cpaGBe7GJxqulYr+FBVvImqQWrE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type:content-transfer-encoding; b=lmXxxCvEWXe225v7MjVyNfZVHVI876EaLCV8YR+Vf/TmR/UpNacXSRmVO29b4TZ4RK 9twODj7ftVQ7N56SXs1scePFBswBYWGhCgTXzRNgFEnA9T0JNxzebjMCxR05TMtYPTEB gKHTir41x/dnqU/ho8bkZERNgm9weN8+WtV4M= Received: by 10.213.100.165 with SMTP id y37mr483924ebn.71.1266927498811; Tue, 23 Feb 2010 04:18:18 -0800 (PST) Received: from ?0.0.0.0? (5357DA75.cable.casema.nl [83.87.218.117]) by mx.google.com with ESMTPS id 7sm9440901eyb.18.2010.02.23.04.18.17 (version=TLSv1/SSLv3 cipher=RC4-MD5); Tue, 23 Feb 2010 04:18:17 -0800 (PST) Message-ID: <4B83C78B.6000206@gmail.com> Date: Tue, 23 Feb 2010 13:18:19 +0100 From: Yeb Havinga User-Agent: Thunderbird 2.0.0.23 (Windows/20090812) MIME-Version: 1.0 To: Glenn Maynard CC: pgsql-performance@postgresql.org Subject: Re: Slow query: table iteration (8.3) References: <603c8f071002031905w51dbea37y148284ea6546feda@mail.gmail.com> <2f4958ff1002040028j29e907c6l47e1f5efe83b667d@mail.gmail.com> In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.403 tagged_above=-10 required=5 tests=AWL=0.196, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201002/319 X-Sequence-Number: 37740 Glenn Maynard wrote: > CREATE FUNCTION high_score_for_user(user_id int) RETURNS SETOF INTEGER > LANGUAGE SQL AS $$ > SELECT t.id FROM test t > WHERE t.user_id = $1 > ORDER BY t.score DESC LIMIT 1 > $$; > SELECT high_score_for_user(u.id) FROM test_users u; > > runs in 100ms. > Though it doesn't solve your problem without changing result format, but what about aap=# explain select u.id, ARRAY(select t.id from test t where t.user_id=u.id order by t.score desc limit 2) as high from test_users u; QUERY PLAN ----------------------------------------------------------------------------------------- Seq Scan on test_users u (cost=0.00..3290.84 rows=1000 width=4) SubPlan 1 -> Limit (cost=0.00..3.28 rows=2 width=8) -> Index Scan using test_2 on test t (cost=0.00..1637.92 rows=1000 width=8) Index Cond: (user_id = $0) (5 rows) id | high ------+----------------- 1 | {641,896} 2 | {1757,1167} 3 | {2765,2168} 4 | {3209,3674} 5 | {4479,4993} regards, Yeb Havinga