Complicated article sort

Question

In short I have a bunch of articles (~100k) which shall be ranked ("mixed", hence "newsmix") based on their newsValue (how important they are, between 1 and 10) and newsLifetime (how long they stay important, between 1 and 4), how long ago since published, and the users' preference for each category. It also groups them according to which story they belong to.

My main concern is that the query way too slow (~200ms), and thus I'm looking for ways to optimize the following query.

select array_to_json(array_agg(stories.articles)) articles from (
  select array_to_json((array_agg(articles order by articles.newsmixValue desc))[1:3]) articles from (
    select * from (
      select a.*,
      (10 * a.breaking::integer) + ((a.data->'newsValue')::text::float / 10 * (n.weight + u.weight) - ((floor(extract('epoch' from $1::timestamptz - a.published::timestamptz) / 60) / 1080) * ((4 - (a.data->'newsLifetime')::text::float - 2.0) * 0.3 + 2.0))) newsmixValue
      from (
        select
          articles.*,
          'Just nu' in (select value->>'title' from json_array_elements((data->'topics')::json)) as breaking,
          s.id as "storyId",
          s.title as "storyTitle"
        from articles
        left join stories s
          on articles.data->>'storyId' = s.id
        where published is not null
          and is_deleted=false
          and published::timestamptz < $1::timestamptz
          and (
            published::timestamptz > $1::timestamptz::date - $7::integer
            -- setting 0 means no date-limit
            or $7 = 0
          )
          and category_id = any ($6)
        order by published desc
      ) a
      left join (
        select usermix.key category_id, usermix.value::text::float weight from json_each($2) usermix
      ) u
      on a.category_id=u.category_id
      left join (
        select newsmix.key category_id, newsmix.value::text::float weight from json_each($3) as newsmix
      ) n
      on a.category_id=n.category_id
      left join (
        select threshold.key category_id, threshold.value::integer weight from json_each_text($8) as threshold
      ) t
      on a.category_id=t.category_id
      where (a.data->>'newsValue')::float >= t.weight
    ) aa
    order by aa.newsmixValue desc
  ) articles
  group by COALESCE((articles.data->'storyId')::text, articles.id::text)
  order by max(articles.newsmixValue) desc
  limit $4
  offset $5
) as stories

where

$1 is a timestamp

$2 is a json-object where the key is a category id and the value the users preference

$3 is a json-object where the key is a category id and the value a (mostly) static relative weighing of categories

$4 is limit

$5 is offset

$6 is an array of categories articles must have to be selected

$7 is how many days older than $1 an article may be

$8 is a json-object where the key is a category id and the value is a limit for how low the newsValue may be for that category

The expected result of the ranking and aggregation, to demonstrate what the query does:

[[{id: 123, story: 11, newsmixValue: 1.00}, {id: 234, story: 11, newsmixValue: -123.00}], [{id: 345, story: null, newsmixValue: 0.89}], [{id: 456, story: 5, newsmixValue: 0.87}, {id: 567, story: 5, newsmixValue: -1.00}], [etc]]

My attempts so far includes limiting the inner sub query to the last week (see $1 and $7 above) which makes it at least not take 11 seconds. I'd be great if that wasn't necessary though. I think I've indexed the relevant fields:

# \d+ articles
                                  Table "public.articles"
   Column    |           Type           | Modifiers | Storage  | Stats target | Description 
-------------+--------------------------+-----------+----------+--------------+-------------
 id          | text                     | not null  | extended |              | 
 stable_id   | text                     |           | extended |              | 
 category_id | text                     |           | extended |              | 
 story_id    | text                     |           | extended |              | 
 updated     | timestamp with time zone |           | plain    |              | 
 published   | timestamp with time zone |           | plain    |              | 
 is_deleted  | boolean                  |           | plain    |              | 
 data        | jsonb                    |           | extended |              | 
Indexes:
    "temp_articles_pkey1" PRIMARY KEY, btree (id)
    "articles_id_idx" UNIQUE, btree (id)
    "articles_category_id_idx" btree (category_id)
    "articles_expr_idx3" btree ((data ->> 'storyId'::text))
    "articles_is_deleted_idx" btree (is_deleted)
    "articles_published_idx" btree (published)

Explain analyze gives me:

"Limit  (cost=3383.34..3383.59 rows=100 width=1621) (actual time=178.959..178.982 rows=100 loops=1)"
"  ->  Sort  (cost=3383.34..3384.01 rows=268 width=1621) (actual time=178.958..178.970 rows=100 loops=1)"
"        Sort Key: (max(articles.newsmixvalue))"
"        Sort Method: top-N heapsort  Memory: 568kB"
"        ->  GroupAggregate  (cost=3364.39..3373.10 rows=268 width=1621) (actual time=142.062..177.978 rows=540 loops=1)"
"              Group Key: (COALESCE(((articles.data -> 'storyId'::text))::text, articles.id))"
"              ->  Sort  (cost=3364.39..3365.06 rows=268 width=1621) (actual time=141.938..142.099 rows=894 loops=1)"
"                    Sort Key: (COALESCE(((articles.data -> 'storyId'::text))::text, articles.id))"
"                    Sort Method: quicksort  Memory: 3573kB"
"                    ->  Subquery Scan on articles  (cost=3348.22..3353.58 rows=268 width=1621) (actual time=108.703..130.356 rows=894 loops=1)"
"                          ->  Sort  (cost=3348.22..3348.89 rows=268 width=956) (actual time=108.647..108.803 rows=894 loops=1)"
"                                Sort Key: ((((10 * (((SubPlan 1)))::integer))::double precision + ((((((articles_1.data -> 'newsValue'::text))::text)::double precision / 10::double precision) * (((newsmix.value)::text)::double precision + ((usermix.value): (...)"
"                                Sort Method: quicksort  Memory: 605kB"
"                                ->  Hash Left Join  (cost=3270.08..3337.41 rows=268 width=956) (actual time=67.891..107.331 rows=894 loops=1)"
"                                      Hash Cond: (articles_1.category_id = newsmix.key)"
"                                      ->  Hash Left Join  (cost=3267.82..3306.68 rows=268 width=924) (actual time=67.790..82.502 rows=894 loops=1)"
"                                            Hash Cond: (articles_1.category_id = usermix.key)"
"                                            ->  Hash Join  (cost=3265.57..3300.75 rows=268 width=892) (actual time=67.753..81.675 rows=894 loops=1)"
"                                                  Hash Cond: (articles_1.category_id = threshold.key)"
"                                                  Join Filter: (((articles_1.data ->> 'newsValue'::text))::double precision > ((threshold.value)::integer)::double precision)"
"                                                  Rows Removed by Join Filter: 68"
"                                                  ->  Sort  (cost=3263.32..3265.33 rows=804 width=891) (actual time=67.686..67.877 rows=962 loops=1)"
"                                                        Sort Key: articles_1.published"
"                                                        Sort Method: quicksort  Memory: 611kB"
"                                                        ->  Hash Left Join  (cost=171.60..3224.52 rows=804 width=891) (actual time=3.740..66.424 rows=962 loops=1)"
"                                                              Hash Cond: ((articles_1.data ->> 'storyId'::text) = s.id)"
"                                                              ->  Bitmap Heap Scan on articles articles_1  (cost=22.63..2448.96 rows=804 width=832) (actual time=0.856..2.225 rows=962 loops=1)"
"                                                                    Recheck Cond: ((published IS NOT NULL) AND (published < now()) AND (published > ((now())::date - 7)))"
"                                                                    Filter: ((NOT is_deleted) AND (category_id = ANY ('{36480a9056b9423daf4538f7aa62bad2,feddd38a2f914a06a6f851eeb32a7eb7,ea7e9c35b0ca4f36bf80cda5a55bbc68,c3708684382b4848b740652385dc25a8,9ab3 (...)"
"                                                                    Rows Removed by Filter: 6"
"                                                                    Heap Blocks: exact=176"
"                                                                    ->  Bitmap Index Scan on articles_published_idx  (cost=0.00..22.43 rows=810 width=0) (actual time=0.816..0.816 rows=1822 loops=1)"
"                                                                          Index Cond: ((published IS NOT NULL) AND (published < now()) AND (published > ((now())::date - 7)))"
"                                                              ->  Hash  (cost=100.10..100.10 rows=3910 width=59) (actual time=2.757..2.757 rows=3041 loops=1)"
"                                                                    Buckets: 1024  Batches: 1  Memory Usage: 272kB"
"                                                                    ->  Seq Scan on stories s  (cost=0.00..100.10 rows=3910 width=59) (actual time=0.006..1.003 rows=3041 loops=1)"
"                                                              SubPlan 1"
"                                                                ->  Function Scan on json_array_elements  (cost=0.01..1.26 rows=100 width=32) (actual time=0.032..0.040 rows=4 loops=962)"
"                                                  ->  Hash  (cost=1.00..1.00 rows=100 width=64) (actual time=0.025..0.025 rows=6 loops=1)"
"                                                        Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"                                                        ->  Function Scan on json_each_text threshold  (cost=0.00..1.00 rows=100 width=64) (actual time=0.020..0.022 rows=6 loops=1)"
"                                            ->  Hash  (cost=1.00..1.00 rows=100 width=64) (actual time=0.025..0.025 rows=6 loops=1)"
"                                                  Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"                                                  ->  Function Scan on json_each usermix  (cost=0.00..1.00 rows=100 width=64) (actual time=0.019..0.020 rows=6 loops=1)"
"                                      ->  Hash  (cost=1.00..1.00 rows=100 width=64) (actual time=0.019..0.019 rows=6 loops=1)"
"                                            Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"                                            ->  Function Scan on json_each newsmix  (cost=0.00..1.00 rows=100 width=64) (actual time=0.012..0.014 rows=6 loops=1)"
"Planning time: 2.609 ms"
"Execution time: 179.315 ms"

Which doesn't help me much.

TLDR

• How can I make this faster?
• Is there any function/concept that would make this kind of aggregation and sorting simpler?

Answer 1

Indexes:
    "temp_articles_pkey1" PRIMARY KEY, btree (id)
    "articles_id_idx" UNIQUE, btree (id)
    "articles_category_id_idx" btree (category_id)
    "articles_expr_idx3" btree ((data ->> 'storyId'::text))
    "articles_is_deleted_idx" btree (is_deleted)
    "articles_published_idx" btree (published)

This isn't how indexes normally work. While PostgreSQL apparently supports combining indexes, its method to do so is generally slower than accessing a single index. You don't put one index on each column that you are using. Instead, you build your indexes as subsets of the queried columns. Perhaps something like

    "articles_expr_idx4" btree (category_id, published, (data ->> 'storyId'::text), is_deleted)

Correct order may be different, although your known values are category_id and a range for published. So category_id would normally be first in the index followed by the range.

Note that is_deleted may be better not in the index, since it only has two possible values. You usually want index values to exclude more possibilities. I would remove articles_is_deleted_idx entirely for that reason. You also don't need articles_id_idx as you already have a primary key index on that column.

Also, I'm not a PostgreSQL guy, so apologies if I mangled the syntax.

      left join (
        select threshold.key category_id, threshold.value::integer weight from json_each_text($8) as threshold
      ) t
      on a.category_id=t.category_id
      where (a.data->>'newsValue')::float >= t.weight

The WHERE clause only works if t.weight is not null. But if t.weight is not null, then you don't a LEFT JOIN. You could just use a standard INNER JOIN instead.

As a general rule, inner joins are faster than outer joins (left or right). So if you are experiencing slow results, try replacing the outer joins with inner joins to see if you get the data that you need. Perhaps you need to make a requirement that mix values always exist for every category ID passed in $6. That would allow you to replace the slow left outer joins with fast inner joins.