# calculating the percentage of distribution of ids per Loc for each country

Which of the two queries is theoretically better and why?

I would like to understand why query 2 takes longer although there is no relevant information in the Execution Plan.

I'm querying a Presto database. I am not the owner of the table. The table does not have any keys or indexes. The table has 7,5 million rows.

The query calculates the percentage distribution of ids per Loc for each country.

### Query 1:

WITH a AS (
SELECT title
, country
, loc
, count(DISTINCT id) AS num_id
FROM table
GROUP BY 1,2,3)
SELECT title
, country
, loc
, num_id
, num_id * 100.0000/ (sum(num_id) over(PARTITION BY title, country)) AS percentage
FROM a
GROUP BY 1,2,3,4


### Query 2:

SELECT title
, country
, loc
, count(DISTINCT id) AS num_id
, count(DISTINCT id) * 100.0000/ (sum(count(DISTINCT id)) over(PARTITION BY title, country)) AS percentage
FROM table
GROUP BY 1,2,3


### Table definition

title   varchar
dt  date
div_code    varchar
id  varchar
id_type varchar
country varchar
loc varchar
vo  varchar
ref_title   varchar
mode    varchar
sessions    bigint
ttl_type    bigint


• I've created the tag for you, and added it to the question. Jan 26, 2023 at 10:08
• How many rows are in the table? Jan 26, 2023 at 13:29
• @Reinderien 7.5M rows Jan 26, 2023 at 15:53

would like to understand why query 2 takes longer although there is no relevant information in the Execution Plan.

I'm unwilling to believe there is no relevant information in the Execution Plan. Both the order of operations, and the number rows per operation, are important. But let's forge ahead without that helpful data.

You made it clear that, at minimum, we will be table scanning.

Here are the "hard" items retrieved:

    , count(DISTINCT id) AS num_id
...
, num_id * 100.0000/ (sum(num_id) over(PARTITION BY title, country)) AS percentage


We need to scan all IDs and sort them or put them in a hash table (you elided Explain Plan results so it's unclear which). And we need to essentially GROUP BY title, country.

You could create derived reporting tables to study the cost of those operations. Consider creating one which has an INDEX on (title, country).

Well, the first is pretty explicitly asking the backend to compute num_id just once in an intermediate relation, while the second leaves it to the optimizer to notice that, under certain query isolation parameters, count(DISTINCT id) won't vary with title / country. You appear to be disappointed that it didn't notice that. I believe we're doing repeated unnecessary scans to compute that count.