Search Postgresql Archives

Re: newsfeed type query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello.


On 29.4.2015 17:27, Jonathan Vanasco wrote:
> 
> 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 data from another

Hmm, I don't understand why it should behave like that. Imagine the following
postings (ts is a relative timestamp):

posting  ts  context
p0        0  friend
p10      10  group
p20      20  friend
p30      30  group
p40      40  friend
p50      50  group
p60      60  friend

and let's say the LIMIT is 2. Then:
* The first subquery (for friends) should return p60 and p40 (in DESC order).
* The second subquery (for groups) should return p50 and p30 (in DESC order).
* The UNION should return p60 and p50.

Could you please explain to me the error(s) in my reasoning?

Thank you,

Ladislav Lenart


> 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; limiting the 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 a bigserial, relationship tables (friends, memberships) use a composite key ).  They are aggressively indexed and reindexed on various columns for query performance.  sometimes we create an extra index that has multiple columns or partial-columns to make make scans index-only.
> 
> 
> 
> 
> 
> 
> 
> 
> 




-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux