Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE article (
   id      int  PRIMARY KEY 
 , article text NOT NULL
);

INSERT INTO article VALUES
  (362718,'foo')
, (362719,'bar')
, (362729,'baz')
, (362739,'nope1')
, (362749,'nope1');

CREATE TABLE prediction (
   id                     int  PRIMARY KEY 
 , article_id             int  NOT NULL
 , predicted_as           text NOT NULL
 , prediction_date        date NOT NULL
 , article_published_date date NOT NULL
);

INSERT INTO prediction VALUES
  (1009381,362718,'negative','2018-07-27','2018-06-26')
, (1009382,362718,'positive','2018-08-12','2018-06-26')
, (1009383,362719,'positive','2018-08-13','2010-09-22')
, (1009384,362719,'positive','2018-09-28','2010-09-22')
, (1009385,362719,'negative','2018-10-01','2010-09-22')

, (1009386,362719,'positive','2018-01-28','2018-06-26')  -- more rows
, (1009387,362719,'negative','2018-01-01','2018-06-27')  -- for meaningful test
, (1009388,362729,'negative','2018-01-01','2019-06-27')
, (1009389,362739,'negative','2018-01-01','2007-06-27')
, (1009390,362749,'negative','2018-01-01','2007-06-27');

CREATE INDEX ON prediction (article_published_date DESC, prediction_date DESC, article_id DESC);
CREATE INDEX ON prediction (article_id, prediction_date desc);


-- alternative: function
CREATE OR REPLACE FUNCTION f_top_n_predictions(_n int = 3)
  RETURNS TABLE (_article_published_date date, _article_id int, _prediction_date date) AS
$func$
DECLARE
   a_ids int[];
BEGIN
   FOR _article_published_date, _article_id, _prediction_date IN
      SELECT article_published_date, article_id, prediction_date
      FROM   prediction
      ORDER  BY article_published_date DESC, prediction_date DESC, article_id DESC
   LOOP
      IF _article_id = ANY(a_ids)
      OR EXISTS (SELECT FROM prediction p
                 WHERE  p.article_id = _article_id
                 AND    p.prediction_date > _prediction_date) THEN
         -- do nothing         
      ELSE
         RETURN NEXT;
         a_ids := a_ids || _article_id;
         EXIT WHEN cardinality(a_ids) >= _n;
      END IF;
   END LOOP;
END
$func$  LANGUAGE plpgsql;
5 rows affected

10 rows affected

WITH RECURSIVE cte AS (
   (
   SELECT p.article_published_date, p.article_id, p.prediction_date, ARRAY[p.article_id] AS a_ids
   FROM   prediction p
   WHERE  NOT EXISTS (  -- no later row for same article
      SELECT FROM prediction
      WHERE  article_id = p.article_id
      AND    prediction_date > p.prediction_date
      )
   ORDER  BY p.article_published_date DESC, p.prediction_date DESC, p.article_id DESC
   LIMIT  1
   )
   UNION ALL
   SELECT p.article_published_date, p.article_id, p.prediction_date, a_ids || p.article_id
   FROM   cte c, LATERAL (
      SELECT p.article_published_date, p.article_id, p.prediction_date
      FROM   prediction p
      WHERE (p.article_published_date, p.prediction_date, p.article_id)
          < (c.article_published_date, c.prediction_date, c.article_id)
      AND    p.article_id <> ALL(a_ids)   -- different article
      AND    NOT EXISTS (                 -- no later row for same article
         SELECT FROM prediction
         WHERE  article_id = p.article_id
         AND    prediction_date > p.prediction_date
         )
      ORDER  BY p.article_published_date DESC, p.prediction_date DESC, p.article_id DESC
      LIMIT  1
      ) p
   )
SELECT article_published_date, article_id, prediction_date
FROM   cte
LIMIT  3;


article_published_date
article_id
prediction_date


2019-06-27
362729
2018-01-01

2018-06-26
362718
2018-08-12

2010-09-22
362719
2018-10-01


SELECT * FROM f_top_n_predictions();


_article_published_date
_article_id
_prediction_date


2019-06-27
362729
2018-01-01

2018-06-26
362718
2018-08-12

2010-09-22
362719
2018-10-01


SELECT p.*
FROM   article a
CROSS  JOIN LATERAL (
   SELECT p.article_published_date, p.article_id, p.prediction_date
   FROM   prediction p
   WHERE  p.article_id = a.id
   ORDER  BY p.prediction_date DESC
   LIMIT  1
   ) p
ORDER  BY p.article_published_date DESC
LIMIT  3;


article_published_date
article_id
prediction_date


2019-06-27
362729
2018-01-01

2018-06-26
362718
2018-08-12

2010-09-22
362719
2018-10-01
article_published_date	article_id	prediction_date
2019-06-27	362729	2018-01-01
2018-06-26	362718	2018-08-12
2010-09-22	362719	2018-10-01