Skip to main content

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.

Benefit​

Projection Pushdown will enable other optimizations in PawSQL and in database engine:

  • Optimizations enabled in PawSQL

    • index recommendation of index-only strategy

  • Optimizations enabled in Database engine

    • index only access in query plan

Conditions​

  • There is no star, except for count(*), in outer query block,
  • There is no star in the derived table or the common table expression being projection-pushdowned
  • There are some select elements in a derived table or a common table expression which are not referenced in outer query block.

Example​

  • Derived Table

    • original query
    select c.c_name, sum(o_totalprice) price FROM customer c, (select c_name, o_custkey, o_totalprice from orders where o_orderpriority=0) dt 
    where c.c_custkey = dt.o_custkey group by c.c_name
    • rewritten query
    select c.c_name, sum(dt.o_totalprice) as price
    from customer as c, (
    select orders.o_custkey, orders.o_totalprice
      from orders
      where orders.o_orderpriority = 0) as dt
    where c.c_custkey = dt.o_custkey
    group by c.c_name

  • Common Table Expression

    • original query
    with dt as ( select c_name, o_custkey, o_totalprice from orders where o_orderpriority=0)
    select c.c_name, sum(o_totalprice) price from customer c, dt where c.c_custkey = dt.o_custkey group by c.c_name
    • rewritten query
    with dt as
    (select orders.o_custkey, orders.o_totalprice
        from orders
        where orders.o_orderpriority = 0)
    select c.c_name, sum(dt.o_totalprice) as price
    from customer as c, dt
    where c.c_custkey = dt.o_custkey
    group by c.c_name

About PawSQL​

PawSQL is dedicated to automatic and intelligent database performance optimization. The products provided by PawSQL include:

  • PawSQL Cloud, an online automated SQL optimization tool that supports SQL auditing, intelligent query rewriting, cost-based index recommendations, suitable for database administrators and data application developers.
  • PawSQL Advisor, an IntelliJ plugin that is suitable for data application developers and can be installed via the IDEA/DataGrip marketplace by searching for "PawSQL Advisor" by name.
  • PawSQL Engine, which is the backend optimization engine of the PawSQL series of products, can be installed and deployed independently, and provides SQL optimization services through http/json interfaces. PawSQL Engine is provided for deployment and installation as a docker image.

Contact Us​

WebSite: https://docs.pawsql.com

Email: service@pawsql.com

Twitter: https://twitter.com/pawsql