CREATE TABLE public.test (
  id SERIAL,
  article INTEGER NOT NULL,
  dealer INTEGER NOT NULL,
  price NUMERIC(12,2) NOT NULL,
  PRIMARY KEY(id)
) ;


CREATE OR REPLACE FUNCTION public.test_filler ()
RETURNS void AS
$body$
DECLARE
  i INTEGER = 0;
BEGIN
    WHILE i < 1000000
    LOOP
        INSERT INTO test (article, dealer, price) VALUES (CEIL(RANDOM() * 9999), CEIL(RANDOM() * 999), RANDOM() * 9999);
        i = i + 1;
    END LOOP;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;


CREATE INDEX test_article_idx ON public.test
  USING btree (article);


SELECT test_filler()


/**********************************************************************/

SELECT article, dealer, price
FROM   test s1
WHERE  price=(SELECT MAX(s2.price)
    FROM test s2
    WHERE s1.article = s2.article);
-- 9999 rows returned (execution time: 00:01:23; total time: 00:01:23)


CREATE INDEX test_article_price_idx ON public.test
  USING btree (article, price);


SELECT s1.article, dealer, s1.price
FROM test s1
JOIN (
    SELECT article, MAX(price) AS price
    FROM test
    GROUP BY article
) AS s2 ON s1.article = s2.article AND s1.price = s2.price;
-- 9999 rows returned (execution time: 375 ms; total time: 375 ms)


SELECT s1.article, s1.dealer, s1.price
FROM test s1
LEFT JOIN test s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL;
-- 9999 rows returned (execution time: 1,859 sec; total time: 1,875 sec)