AI Chat With DBeaver Community Edition

 DBeaver recently introduced interactive chat capabilities into the free, open-source Community Edition. What does that mean? 

It means some of your database tasks can be accomplished significantly faster! 

First, you can ask for information without coding SQL. I love Structured Query Language, but it is much faster to write this prompt:

What are the top ten most popular rentals and how much revenue did they generate?

And faster than I look at the schema, the AI answers:



-- This query finds the top 10 most rented films and the total revenue they generated.



select

f.film_id,

f.title,

count(r.rental_id) as rental_count,

coalesce(sum(p.amount), 0) as total_revenue

from film f

join inventory i on f.film_id = i.film_id

join rental r on i.inventory_id = r.inventory_id

left join payment p on r.rental_id = p.rental_id

group by f.film_id, f.title

order by rental_count desc, total_revenue desc

limit 10;


BTW - I am using the DVD Rental Database for PostgreSQL, DBeaver Community Edition, 26.1.3, and Github Copilot gpt-4.1 for this blog.


Second, does this query perform as well as it could:


Am I missing any indexes that could speed this query up?


Great question! For the query:

  • You are joining on:  

    • film.film_id = inventory.film_id
    • inventory.inventory_id = rental.inventory_id
    • rental.rental_id = payment.rental_id

  • You are grouping by:  

    • film.film_id, film.title

Recommended Indexes

To optimize this query, you should have indexes on the columns used in joins:

-- For joining inventory to film

create index if not exists idx_inventory_film_id on inventory(film_id);

-- For joining rental to inventory

create index if not exists idx_rental_inventory_id on rental(inventory_id);

-- For joining payment to rental

create index if not exists idx_payment_rental_id on payment(rental_id);


Why These Indexes?

  • inventory(film_id): Quickly finds all inventory items for a film.
  • rental(inventory_id): Quickly finds all rentals for an inventory item.
  • payment(rental_id): Quickly finds all payments for a rental.

Additional Notes

  • If you have foreign key constraints, these indexes may not exist automatically in PostgreSQL (unlike some other databases).
  • You can check for existing indexes with:
select * from pg_indexes where tablename in ('inventory', 'rental', 'payment');
  • If your tables are small, the impact will be less, but for large tables, these indexes are important.

Summary:
If you do not already have indexes on inventory(film_id), rental(inventory_id), and payment(rental_id), you should add them to speed up this query.

Let me know if you want to check for existing indexes or need help creating them!


Want a Demo??


🪄Join a webinar on 10 ways to use AI with your databases. They range from generating SQL in plain language to connecting AI tools to your database via MCP. If you’re using DBeaver Community or DBeaver PRO, you can learn something new. 📅 Aug 4 at 11 AM CT




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