Re: newsfeed type query

Thanks all!  These point me in much better directions!

Jim Nasby's approach to selecting an expression addressed some things (SELECT f.useraccount_id_b IS NOT NULL AS
in_friends)

Ladislav Lenart's usage of the CTE is also of a different format that I've used in the past.

I think i'll be able to patch together some performance improvements now, that will last until the database structure
changes.  


On Apr 29, 2015, at 6:54 AM, Ladislav Lenart wrote:

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


It behaves a lot better, but doesn't give me the resultset I need.  Older data from one subquery is favored to newer
datafrom another 

I use a similar approach on another part of this application -- where the effect on the resultset isn't as pronounced.

On that query there are over 100 million total stream events.  Not using an inner limit runs the query in 7 minutes;
limitingthe inner subquery to 1MM runs in 70 seconds... and limiting to 10k is around 100ms.   


On Apr 29, 2015, at 10:16 AM, Melvin Davidson wrote:

> I see others have responded with suggestions to improve query performance,
> but one thing I noticed when you gave the data structure is there are no
> no primary keys defined for friends or posting,  neither are there any indexes.
> Was that an omission?

This was a quick functional example to illustrate.  The real tables are slightly different but do have pkeys ( 'id' is
abigserial, relationship tables (friends, memberships) use a composite key ).  They are aggressively indexed and
reindexedon various columns for query performance.  sometimes we create an extra index that has multiple columns or
partial-columnsto make make scans index-only.