Skip site navigation (1) Skip section navigation (2)

Re: Slow query with backwards index scan

From: andrew(at)pillette(dot)com
To: Tilmann Singer <tils-pgsql(at)tils(dot)net>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Slow query with backwards index scan
Date: 2007-07-28 19:03:52
Message-ID: 200707281903.l6SJ3qs07818@pillette.com (view raw or flat)
Thread:
Lists: pgsql-performance
Tilmann Singer <tils-pgsql(at)tils(dot)net> wrote ..
> * Nis Jrgensen <nis(at)superlativ(dot)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;

Responses

pgsql-performance by date

Next:From: Tilmann SingerDate: 2007-07-28 19:27:13
Subject: Re: Slow query with backwards index scan
Previous:From: Jim C. NasbyDate: 2007-07-28 16:36:24
Subject: Re: Vacuum looping?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group