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:
>>
>> http://g
* 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:
>
> http://groups.google.dk/group/pgsql.
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)
>
* Craig James <[EMAIL PROTECTED]> [20070728 22:00]:
> >>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
> >
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 pa
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
(S
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
(S
* [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 *
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 u
* 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=1234
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 =
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 of
12 matches
Mail list logo
