Postgres 15

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?.

select version();


version


PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


CREATE TABLE brostat (
region VARCHAR(30),
browser VARCHAR(30)
);

INSERT INTO brostat VALUES
('Adygea', 'Chrome'),
('Adygea', 'Firefox'),
('Adygea', 'Firefox'),
('Adygea', 'Firefox'),
('Adygea', 'Firefox'),
('Adygea', 'Firefox'),
('Adygea', 'Firefox'),
('Adygea', 'Firefox'),
('Adygea', 'Firefox'),
('Adygea', 'Firefox'),
('Adygea', 'Firefox'),
('Adygea', 'Opera'),
('Adygea', 'Opera'),
('Adygea', 'Safari'),
('Adygea', 'Safari'),
('Adygea', 'Safari'),
('Adygea', 'Safari'),
('Altai', 'Chrome'),
('Altai', 'Chrome'),
('Altai', 'Chrome'),
('Altai', 'Chrome'),
('Altai', 'Firefox'),
('Altai', 'Firefox'),
('Altai', 'Firefox'),
('Altai', 'Firefox'),
('Altai', 'Firefox'),
('Altai', 'Opera'),
('Altai', 'Opera'),
('Altai', 'Safari');

CREATE TABLE



INSERT 0 29


SELECT region, browser, count(1) cnt
  FROM brostat
  GROUP BY region, browser
;


region
browser
cnt


Adygea
Chrome
1

Adygea
Firefox
10

Altai
Opera
2

Altai
Chrome
4

Adygea
Safari
4

Altai
Safari
1

Adygea
Opera
2

Altai
Firefox
5



SELECT 8


SELECT
    region, browser, cnt
  FROM (
    SELECT
      t.* , ROW_NUMBER() OVER (PARTITION BY region ORDER BY cnt DESC) n
      FROM (
        SELECT region, browser, count(1) cnt
          FROM brostat
          GROUP BY region, browser
    ) t
  ) tt WHERE n = 1
;


region
browser
cnt


Adygea
Firefox
10

Altai
Firefox
5



SELECT 2


SELECT
  region, browser, count(1) cnt
  FROM brostat bs
  GROUP BY region, browser
  HAVING count(1) = (
          SELECT MAX(cnt)
            FROM (
              SELECT
                region, browser, count(1) cnt
              FROM brostat
              GROUP BY region, browser
              ) t
            WHERE t.region = bs.region
            GROUP BY region
        )
;


region
browser
cnt


Adygea
Firefox
10

Altai
Firefox
5



SELECT 2

region	browser	cnt
Adygea	Chrome	1
Adygea	Firefox	10
Altai	Opera	2
Altai	Chrome	4
Adygea	Safari	4
Altai	Safari	1
Adygea	Opera	2
Altai	Firefox	5