21 posts tagged with "optimize"

Copyright © 2024 PawSQL

explain analyze 
select * from orders where o_orderdate < all(select o_orderdate from orders where o_custkey > 100)
;

-> Filter: <not>((orders.O_ORDERDATE >= (select #2)))  (cost=13846.04 rows=133181) (actual time=672.536..672.536 rows=0 loops=1)
    -> Table scan on orders  (cost=13846.04 rows=199762) (actual time=2.460..408.819 rows=201800 loops=1)
    -> Select #2 (subquery in condition; run only once)
        -> Aggregate: min(orders.O_ORDERDATE)  (cost=30281.45 rows=1) (actual time=214.765..214.765 rows=1 loops=1)
            -> Filter: (orders.O_CUSTKEY > 100)  (cost=20293.35 rows=99881) (actual time=0.237..202.460 rows=199252 loops=1)
                -> Covering index range scan on orders using comp_idx over (100 < O_CUSTKEY)  (cost=20293.35 rows=99881) (actual time=0.138..189.628 rows=199252 loops=1)

Copyright © 2024 PawSQL

Definition​

By enabling Projection Pushdown rule, PawSQL eliminates the columns in derived tables, common table expressions and views(after resolving), which are not referenced in outer query blocks, to:

• reduce the IO cost
• reduce the network cost
• enable index recommendation of index-only strategy

This justification is similar to rule [RuleStarInSelectList], but PawSQL supplies a rewrite optimization instead of only warning for star in select list scenario.

We will use derived table in following paragraph, but all the descriptions also apply to common table expressions and resolved views.

Copyright © 2024 PawSQL

Types of Joins​

The types of SQL joins mainly include inner join, outer join (left outer join, right outer join, full outer join), and cross join. C.L. Moffatt explains them very nicely in his article Visual Representation of SQL Joins in a visual way. You will find the information you need in following diagram he presented in his article.

How does Join Type affect Query Execution​

Copyright © 2024 PawSQL

Problem Definition​

If two query conditions are connected by OR, the database optimizer may not be able to use indexes to execute the query. For example, for the following SQL statement:

select * from customer where c_phone like '139%' or c_name = 'Ray' 

Even if both fields have indexes, they can be rewritten as a UNION query to leverage the indexes and improve query performance:

Copyright © 2024 PawSQL

Definition​

Join Elimination is a rewriting optimization in SQL that simplifies queries and improves query performance by removing joins from the query without affecting the final result. Typically, this optimization is used when a query contains a primary-foreign key join and only references the primary key columns of the main table.

Consider the following example:

select o.* from orders o inner join customer c on c.c_custkey=o.o_custkey

The orders table is joined with the customer table, and c_custkey is the primary key of the customer table. In this case, the customer table can be eliminated, and the rewritten SQL would be:

select * from orders where o_custkey