Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 5CA019FB2C6 for ; Sat, 28 Jul 2007 17:04:40 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 96875-03 for ; Sat, 28 Jul 2007 17:04:37 -0300 (ADT) X-Greylist: delayed 00:24:21.02563 by SQLgrey-1.7.5 Received: from wizmail.org (wizmail.org [217.146.107.12]) by postgresql.org (Postfix) with ESMTP id 3CEFD9FB1DC for ; Sat, 28 Jul 2007 17:04:37 -0300 (ADT) Received: from ebony.jgh.adsl.wizards.co.uk ([217.146.123.59]) (from_AS 16353) by wizmail.org with esmtpsa (TLSv1:DHE-RSA-AES256-SHA:256) (Exim 4.67) id 1IEs9H-00015v-2G for pgsql-performance@postgresql.org (return-path ); Sat, 28 Jul 2007 19:40:23 +0000 Message-ID: <46AB9B9C.8070009@wizmail.org> Date: Sat, 28 Jul 2007 20:40:12 +0100 From: Jeremy Harris User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.8.0.12) Gecko/20070530 Fedora/1.5.0.12-1.fc6 pango-text Thunderbird/1.5.0.12 Mnenhy/0.7.5.0 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Slow query with backwards index scan References: <200707281903.l6SJ3qs07818@pillette.com> <20070728192713.GH19392@tils.net> In-Reply-To: <20070728192713.GH19392@tils.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.269 tagged_above=0 required=5 tests=AWL=0.268, BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200707/402 X-Sequence-Number: 26086 Tilmann Singer wrote: > * andrew@pillette.com [20070728 21:05]: >> Let's try putting the sort/limit in each piece of the UNION to speed them up separately. >> >> SELECT * FROM ( >> (SELECT * FROM large_table lt >> WHERE lt.user_id = 12345 >> ORDER BY created_at DESC LIMIT 10) AS q1 >> UNION >> (SELECT * FROM large_table lt >> WHERE user_id IN (SELECT contact_id FROM relationships WHERE user_id=12345) >> ORDER BY created_at DESC LIMIT 10) AS q2 >> ORDER BY created_at DESC LIMIT 10; > > It's not possible to use ORDER BY or LIMIT within unioned queries. > > http://www.postgresql.org/docs/8.2/static/sql-select.html#SQL-UNION "ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses"