Short and Long Queries in PQSL

Let us identify whether a query is a short query or a long query. You will learn how to identify short queries.

What is a short query? First, it has nothing to do with the length of the SQL query.

A query is short when the number of rows needed to compute its output is small, no matter how large the involved tables are. Short queries may read every row from small tables but read only a small percentage of rows from large tables.

Example:
Let's consider a scenario where you have two tables: a small table named small_table and a large table named large_table. The goal is to identify whether a query is a short query based on the criteria you provided.

CREATE TABLE small_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE large_table (
    id SERIAL PRIMARY KEY,
    data VARCHAR(100)
);

Here's an example of a query that reads from both tables and follows the criteria for a short query:

-- Short query example
SELECT *
FROM small_table
UNION ALL
SELECT *
FROM large_table
WHERE id <= 100;

In this example, the query is considered "short" because even though it involves both tables, it reads every row from the small_table and only a small percentage of rows (those with id values less than or equal to 100) from the large_table.

SELECT *
FROM large_table
WHERE data LIKE '%something%';

In this case, the query involves only the large_table, but it reads potentially a large number of rows based on the LIKE condition, so it would not meet the criteria for a "short query."