Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 65DE39FB2E5
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Sat, 28 Jul 2007 16:04:00 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 93550-10 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Sat, 28 Jul 2007 16:03:43 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from pillette.com (adsl-67-119-5-202.dsl.snfc21.pacbell.net
	[67.119.5.202]) by postgresql.org (Postfix) with ESMTP id 24D449FB212
	for <pgsql-performance@postgresql.org>;
	Sat, 28 Jul 2007 16:03:56 -0300 (ADT)
Received: (from andrew@localhost)
	by pillette.com (8.11.6/8.11.6) id l6SJ3qs07818;
	Sat, 28 Jul 2007 12:03:52 -0700
Date: Sat, 28 Jul 2007 12:03:52 -0700
From: andrew@pillette.com
Message-Id: <200707281903.l6SJ3qs07818@pillette.com>
Subject: Re: Slow query with backwards index scan
To: Tilmann Singer <tils-pgsql@tils.net>
Cc: pgsql-performance@postgresql.org
X-Originating-IP: 71.135.49.158
X-Mailer: Webmin 0.940
MIME-Version: 1.0
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.884 tagged_above=0 required=5 tests=AWL=-0.887,
	BAYES_50=0.001, FH_HOST_EQ_PACBELL_D=1.67, RDNS_DYNAMIC=0.1
X-Spam-Level: 
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
X-Archive-Number: 200707/396
X-Sequence-Number: 26080

Tilmann Singer <tils-pgsql@tils.net> wrote ..
> * Nis Jørgensen <nis@superlativ.dk> [20070727 20:31]:
> > How does the "obvious" UNION query do - ie:
> > 
> > SELECT * FROM (
> > SELECT * FROM large_table lt
> > WHERE lt.user_id = 12345
> > 
> > UNION
> > 
> > SELECT * FROM large_table lt
> > WHERE user_id IN (SELECT contact_id FROM relationships WHERE user_id=12345)
> > ) q
> > 
> > ORDER BY created_at DESC LIMIT 10;

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;