Re: newsfeed type query

Hello.

On 29.4.2015 01:57, Jonathan Vanasco wrote:
> Sorry, I was trying to ask something very abstract as I have similar situations
> on multiple groups of queries/tables (and they're all much more complex).
>
> I'm on pg 9.3
>
> The relevant structure is:
>
> posting:
> id
> timestamp_publish
> group_id__in
> user_id__author
>
> friends:
> user_id__a
> user_id__b
>
> memberships:
> user_id
> group_id
> role_id
>
>
> -- working sql
> CREATE TABLE groups(
> id SERIAL NOT NULL PRIMARY KEY
> );
> CREATE TABLE users(
> id SERIAL NOT NULL PRIMARY KEY
> );
> CREATE TABLE friends (
> user_id__a INT NOT NULL REFERENCES users( id ),
> user_id__b INT NOT NULL REFERENCES users( id )
> );
> CREATE TABLE memberships (
> user_id INT NOT NULL REFERENCES users( id ),
> group_id INT NOT NULL REFERENCES groups( id ),
> role_id INT NOT NULL
> );
> CREATE TABLE posting (
> id SERIAL NOT NULL,
> timestamp_publish timestamp not null,
> group_id__in INT NOT NULL REFERENCES groups(id),
> user_id__author INT NOT NULL REFERENCES users(id),
> is_published BOOL
> );
>
> The output that I'm trying to get is:
> posting.id
> {the context of the select}
> posting.timestamp_publish (this may need to get correlated into other queries)
>
>
> These approaches had bad performance:
>
> -- huge selects / memory
> -- it needs to load everything from 2 tables before it limits
> EXPLAIN ANALYZE
> SELECT id, feed_context FROM (
> SELECT id, timestamp_publish, 'in-group' AS feed_context FROM posting
> WHERE (
> group_id__in IN (SELECT group_id FROM memberships WHERE user_id = 57
> AND role_id IN (1,2,3))
> AND (is_published = True AND timestamp_publish <= CURRENT_TIMESTAMP
> AT TIME ZONE 'UTC')
> )
> UNION
> SELECT id, timestamp_publish, 'by-user' AS feed_context FROM posting
> WHERE (
> user_id__author IN (SELECT user_id__b FROM friends WHERE user_id__a
> = 57)
> AND (is_published = True AND timestamp_publish <= CURRENT_TIMESTAMP
> AT TIME ZONE 'UTC')
> )
> ) AS feed
> ORDER BY timestamp_publish DESC
> LIMIT 10
> ;

I think you can propagate ORDER BY and LIMIT also to the subqueries of the
UNION, i.e.:

select...
from (
(
select...
from posting
where... -- friends
order by timestamp_publish desc
limit 10
) union (
(
select...
from posting
where... -- groups
order by timestamp_publish desc
limit 10
)
) as feed
order by timestamp_publish desc
limit 10

That might behave better.

Ladislav Lenart

> -- selects minimized, but repetitive subqueries
> SELECT
> id,
> CASE
> WHEN group_id__in IN (SELECT group_id FROM memberships WHERE user_id
> = 57 AND role_id IN (1,2,3)) THEN True
> ELSE NULL
> END AS feed_context_group,
> CASE
> WHEN user_id__author IN (SELECT user_id__b FROM friends WHERE
> user_id__a = 57) THEN True
> ELSE NULL
> END AS feed_context_user
> FROM posting
> WHERE (
> group_id__in IN (SELECT group_id FROM memberships WHERE user_id = 57
> AND role_id IN (1,2,3))
> OR
> user_id__author IN (SELECT user_id__b FROM friends WHERE user_id__a
> = 57)
> )
> AND (is_published = True AND timestamp_publish <= CURRENT_TIMESTAMP AT
> TIME ZONE 'UTC')
> ORDER BY timestamp_publish DESC
> LIMIT 10
> ;
>
>
>
> On Apr 28, 2015, at 6:56 PM, Melvin Davidson wrote:
>
>> Since you very nicely DID NOT provide the pg version, O/S or table
>> structure(s), which is what you should do REGARDLESS of the
>> type of question (it's just the smart and polite thing to do when asking for
>> help) The best I can suggest is:
>> SELECT
>> CASE WHEN context = 'friend' THEN p.junka
>> WHEN context = 'group' THEN p.junkb
>> WHEN context = 'both' THEN p.junka || ' ' || p.junkb
>> END
>> FROM posting p
>> where p.author_id in (SELECT f.friend_id
>> FROM friends f
>> WHERE f.user_id = ?)
>> OR p.group_id in (SELECT m.group_id
>> FROM memberships m
>> WHERE m.user_id = ?);
>
>