Ever Run Into A PostgreSQL Query That You Can Figure Out What It Does??

 Ever have a query 'tossed over the fence' that you find incomprehensible but still have to support it? A few years ago, you would have needed to triage the query. Obfuscated queries can be tough to decipher.  Sometimes, the query is due to someone or an ORM being clever. Many times the query is touch to read because the 

DBeaver recently added its AI Chat to the free, open-source DBeaver Community Edition. And you will find it very at determining what a query does. Let's start with a simple query.

Open AI Assistant


Where to find the AI Assistant









DBeaver 26.1.2's AI Assistant can be found on the main menu under 'AI'.

Input Prompt

Once you have the AI Assistant open, ask 'what does this do'?
Prompt with a query







Explanation

The AI will examine what you gave it and report back. I am using OpenAI with the gpt-4o engine. 
The Query Explained






































Hopefully, you were able to recognize this simple query, which reports the number of dead tuples. Okay, let us try something harder.

Seeing a query from a production server for the first time can be humbling. A CTE or two, a whole bunch of joins, and a Window Function combined can make quite a head scratcher. 

What does this query do?

with film_revenue as (
    select
        f.film_id,
        f.title,
        c.name as category_name,
        count(r.rental_id) as rental_count,
        sum(p.amount) as total_revenue
    from
        public.film f
    join public.film_category fc on f.film_id = fc.film_id
    join public.category c on fc.category_id = c.category_id
    join public.inventory i on f.film_id = i.film_id
    join public.rental r on i.inventory_id = r.inventory_id
    join public.payment p on r.rental_id = p.rental_id
    group by
        f.film_id, f.title, c.name
),
ranked_films as (
    select
        film_id,
        title,
        category_name,
        rental_count,
        total_revenue,
        row_number() over (partition by category_name order by rental_count desc, total_revenue desc) as rank
    from
        film_revenue
)
select
    title,
    category_name,
    rental_count,
    total_revenue
from
    ranked_films
where
    rank <= 10
order by
    category_name, rank;


So, what is your first conclusion when you see something like this? Pass it to the AI for assistance.

Answer:


This query is designed to find the top 10 most popular films to rent by category, along with the revenue they generate. Here's a breakdown of what each part does:

CTE film_revenue:
  • Purpose: Calculate the total number of rentals and total revenue for each film.
  • Joins:
    • film to film_category to link films with their categories.
    • film_category to category to get the category name.
    • film to inventory to find available copies.
    • inventory to rental to track rentals.
    • rental to payment to calculate revenue.
  • Aggregations:
    • count(r.rental_id): Counts how many times each film was rented.
    • sum(p.amount): Sums up the revenue generated by each film.
  • Grouping: By film ID, title, and category name to ensure accurate aggregation.
CTE ranked_films:
  • Purpose: Rank films within each category based on rental count and revenue.
  • Ranking:
    • row_number() over (partition by category_name order by rental_count desc, total_revenue desc): Assigns a rank to each film within its category, prioritizing higher rental counts and revenue.
Final Select:
  • Purpose: Retrieve the top 10 films for each category.
  • Filter: where rank <= 10 ensures only the top 10 films per category are selected.
  • Order: By category name and rank to organize the output neatly.
This query effectively identifies the most popular films by category and shows how much revenue they generate.

Even more complex!  


Let's get even further off into the weeds of SQL with subqueries. 

select
    x.title,
    x.category_name,
    x.rental_count,
    x.total_revenue
from (
    select
        a.film_id,
        a.title,
        b.name as category_name,
        (select count(*) from public.rental r where r.inventory_id in (select inventory_id from public.inventory where film_id = a.film_id)) as rental_count,
        (select sum(amount) from public.payment p where p.rental_id in (select rental_id from public.rental where inventory_id in (select inventory_id from public.inventory where film_id = a.film_id))) as total_revenue
    from
        public.film a,
        public.film_category fc,
        public.category b
    where
        a.film_id = fc.film_id
        and fc.category_id = b.category_id
) x
where
    (select count(*) from (
        select
            a.film_id,
            b.name as category_name,
            (select count(*) from public.rental r where r.inventory_id in (select inventory_id from public.inventory where film_id = a.film_id)) as rental_count
        from
            public.film a,
            public.film_category fc,
            public.category b
        where
            a.film_id = fc.film_id
            and fc.category_id = b.category_id
    ) y where y.category_name = x.category_name and y.rental_count > x.rental_count) < 10
order by
    x.category_name,
    x.rental_count desc,
    x.total_revenue desc 



Add in some column alias as vague as 'select a.film as RevCntr1' and you have yourself a headache producing query on your hands. The good news is that you can have the AI report on what the query actually does. The AI Assistant was added to the free, open-source DBeaver Community Edition version 26.1.2 and I am sure that you will find it handy when faced with a 'tossed over' query.




Comments

Post a Comment

Popular posts from this blog

How PostgreSQL's Aggregate FILTER Will Spoil You

Using PostgreSQL will definitely spoil you. I recently had to use another database and found, to my chagrin, that aggregate filters were not included in their implementation of Structured Query Language. Specifically, there was no FILTER clause available. FILTER? To illustrate my dependency, let me start with some simple data. demo=# create table z (a int, b int, c int); CREATE TABLE demo=# insert into z values (1,10,100),(2,20,200),(3,30,300),(4,40,400); INSERT 0 4 demo=# select a,b,c from z;  a | b  |  c   ---+----+-----  1 | 10 | 100  2 | 20 | 200  3 | 30 | 300  4 | 40 | 400 (4 rows) demo=#  If I wanted the overall number of rows, I could use a simple COUNT(*). If I wanted an overall number of rows where one of the columns was of a certain value, I could add the logic for getting those rows to the WHERE clause. What if I wanted both?  Do I need two queries to do the work separately? Maybe a materialized view or a CTE (Common Tabl...
Read more

Is the future of MySQL PostgreSQL (Or MariaDB, or TiDB, or ...)?

 I am not intentionally trying to upset anyone with this blog post or minimize the efforts of many brilliant people whom I admire. However, I connected with several people over the 2025 holidays who all had the same question: What is the future of MySQL? At the upcoming FOSDEM conference, several events will discuss this subject and push a particular solution.  And in several ways, they are all wrong. Oracle has not been improving the community edition for a long time now. They have laid off many of their top performers in the MySQL group. We got almost a good decade and a half out of Oracle's stewardship of the "world's most popular database", and we should be thankful for that. However, now that time is over, it is time to consider future options that will involve no updates, CVEs, or innovation for what is the MySQL Community Edition. There are several choices available. Nothing! The first choice is nothing. Many folks run old, end-of-life versions of MySQL. There ...
Read more

PostgreSQL 18 Old & New

 Learning Structured Query Language can be frustrating when double-checking that what you wanted to have done is actually what was done. PostgreSQL 18 has 'OLD and NEW support for RETURNING clauses in INSERT, UPDATE, DELETE, and MERGE commands'. Now you can get instant feedback.  The addition of the RETURNING clause in the previous version made MERGE much easier to use. Now it makes other commands easier. To demonstrate, let's create a table with one column that is designated as a unique, primary key integer and insert a value. create table foo ( a int unique primary key ) ; insert into foo ( a ) values ( 1 ) ; Now is the point where many of us would run a SELECT a FROM foo , just to double check that indeed there is a 1 in column a. There is now an option in PG 18 to use RETURNING, and it provides the previous 'old' value of a column along with the 'new' value. insert into foo ( a ) values ( 2 ) returning old .a, new .a ; a|a| -+-+  |2| The ...
Read more