Re: Slow query with backwards index scan
От | andrew@pillette.com |
---|---|
Тема | Re: Slow query with backwards index scan |
Дата | |
Msg-id | 200707281903.l6SJ3qs07818@pillette.com обсуждение исходный текст |
Ответ на | Slow query with backwards index scan (Tilmann Singer <tils-pgsql@tils.net>) |
Ответы |
Re: Slow query with backwards index scan
|
Список | pgsql-performance |
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;
В списке pgsql-performance по дате отправления: