Tilmann Singer skrev:
But the subselect is not fast for the user with many relationships and
matched rows at the beginning of the sorted large_table:
testdb=# EXPLAIN ANALYZE SELECT * FROM large_table lt
WHERE user_id IN (SELECT contact_id FROM relationships WHERE
user_id=5)
ORDER
* Nis Jørgensen [EMAIL PROTECTED] [20070730 18:33]:
It seems to me the subselect plan would benefit quite a bit from not
returning all rows, but only the 10 latest for each user. I believe the
problem is similar to what is discussed for UNIONs here:
Tilmann Singer skrev:
* Nis Jørgensen [EMAIL PROTECTED] [20070730 18:33]:
It seems to me the subselect plan would benefit quite a bit from not
returning all rows, but only the 10 latest for each user. I believe the
problem is similar to what is discussed for UNIONs here:
* Nis Jørgensen [EMAIL PROTECTED] [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
Tilmann Singer [EMAIL PROTECTED] wrote ..
* Nis Jørgensen [EMAIL PROTECTED] [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
* [EMAIL PROTECTED] [EMAIL PROTECTED] [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
Tilmann Singer wrote:
* [EMAIL PROTECTED] [EMAIL PROTECTED] [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
Tilmann Singer wrote:
* [EMAIL PROTECTED] [EMAIL PROTECTED] [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
As other posters have pointed out, you can overcome the ORDER BY/LIMIT
restriction on UNIONs with parentheses. I think I misbalanced the parentheses
in my original post, which would have caused an error if you just copied and
pasted.
I don't think the limitation has to do with planning--just
Dear list,
I am having problems selecting the 10 most recent rows from a large
table (4.5M rows), sorted by a date column of that table. The large
table has a column user_id which either should match a given user_id,
or should match the column contact_id in a correlated table where the
user_id
Tilmann Singer skrev:
The query works fine for the common cases when matching rows are found
early in the sorted large table, like this:
testdb=# EXPLAIN ANALYZE
SELECT * FROM large_table lt
LEFT JOIN relationships r ON lt.user_id=r.contact_id
WHERE r.user_id = 5 OR lt.user_id = 5
11 matches
Mail list logo
