Skip to main content

Query Folding

· 5 min read
PawSQL Team
PawSQL Team
Optimize your SQL Queries by Clicks!

Copyright © 2024 PawSQL

Definition​

By enabling Query Folding rule, PawSQL merges view references, derived tables and common table expressions to outer query blocks to:

  • avoid materializing intermediate result-set
  • enable more join order planning
  • enable more opportunities for index recommendation

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

Qualified Subquery Rewrite

· 2 min read
PawSQL Team
PawSQL Team
Optimize your SQL Queries by Clicks!

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)

Projection Pushdown

· 3 min read
PawSQL Team
PawSQL Team
Optimize your SQL Queries by Clicks!

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.

Outer to Inner Join Conversion

· 6 min read
PawSQL Team
PawSQL Team
Optimize your SQL Queries by Clicks!

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​

When OR Just Won't Do: Using UNION for Higher SQL Performance

· 4 min read
PawSQL Team
PawSQL Team
Optimize your SQL Queries by Clicks!

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: