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

Question

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

    

Answer 1

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

Now, what's different about your pair of queries?

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.