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

select version();


version


PostgreSQL 11.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


CREATE TABLE IF NOT EXISTS stopAndSearches(
    "sas_id" bigserial PRIMARY KEY,
    "officer_defined_ethnicity" VARCHAR(255),
    "object_of_search" VARCHAR(255),
    "outcome" VARCHAR(255)
);
INSERT INTO stopAndSearches(sas_id, officer_defined_ethnicity, object_of_search, 
outcome) VALUES
(1, 'ethnicity1', 'Psychoactive substances', 'Arrest'),
(2, 'ethnicity1', 'Dog', 'Arrest'),
(3, 'ethnicity2', 'Stolen goods', 'A no further action disposal'),
(4, 'ethnicity3', 'Firearms', 'Arrest'),
(5, 'ethnicity1', 'Fireworks', 'A no further action disposal'),
(6, 'ethnicity2', 'Firearms', 'Arrest'),
(7, 'ethnicity3', 'Fireworks', 'Arrest'),
(8, 'ethnicity1', 'Firearms', 'A no further action disposal'),
(9, 'ethnicity2', 'Stolen goods', 'Arrest'),
(10, 'ethnicity1', 'Cat', 'Arrest')
10 rows affected

SELECT DISTINCT
    stopAndSearches.officer_defined_ethnicity,
    count(stopAndSearches.sas_id) OVER(PARTITION BY stopAndSearches.officer_defined_ethnicity) AS "Sas for ethnicity",
    sum(case when stopAndSearches.outcome = 'Arrest' then 1 else 0 end)
       OVER (PARTITION BY stopAndSearches.officer_defined_ethnicity)::float /
       count(stopAndSearches.sas_id) OVER(PARTITION BY stopAndSearches.officer_defined_ethnicity)::float * 100 AS "Arrest rate",
    mode() WITHIN GROUP (ORDER BY stopAndSearches.object_of_search) AS "Most frequent object of search"
FROM stopAndSearches
GROUP BY stopAndSearches.sas_id, stopAndSearches.officer_defined_ethnicity;


officer_defined_ethnicity
Sas for ethnicity
Arrest rate
Most frequent object of search


ethnicity2
3
66.6666666666667
Stolen goods

ethnicity3
2
100
Fireworks

ethnicity1
5
60
Firearms

ethnicity3
2
100
Firearms

ethnicity1
5
60
Cat

ethnicity1
5
60
Dog

ethnicity2
3
66.6666666666667
Firearms

ethnicity1
5
60
Psychoactive substances

ethnicity1
5
60
Fireworks


WITH cte AS (
        SELECT officer_defined_ethnicity
             , object_of_search
             , COUNT(*) AS n
             , ROW_NUMBER() OVER (PARTITION BY officer_defined_ethnicity ORDER BY COUNT(*) DESC) AS rn
          FROM stopAndSearches
         GROUP BY officer_defined_ethnicity, object_of_search
     )
SELECT * FROM cte
 WHERE rn = 1
;


officer_defined_ethnicity
object_of_search
n
rn


ethnicity1
Cat
1
1

ethnicity2
Stolen goods
2
1

ethnicity3
Fireworks
1
1

officer_defined_ethnicity	Sas for ethnicity	Arrest rate	Most frequent object of search
ethnicity2	3	66.6666666666667	Stolen goods
ethnicity3	2	100	Fireworks
ethnicity1	5	60	Firearms
ethnicity3	2	100	Firearms
ethnicity1	5	60	Cat
ethnicity1	5	60	Dog
ethnicity2	3	66.6666666666667	Firearms
ethnicity1	5	60	Psychoactive substances
ethnicity1	5	60	Fireworks