Skip to main content

Projection Pushdown

Copyright © 2023 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