Skip to main content

Mixed Sort Directions Disable Index Usage

Copyright © 2023 PawSQL

Problem Definition​

For ORDER BY clauses, all expressions must be sorted in the same ASC or DESC direction to utilize indexes. If an ORDER BY statement uses different sort directions on multiple conditions, indexes cannot be used.

For example, creating an index on the lineitem table in TPCH:

create index l_partkey_suppkey_idx on lineitem(l_partkey, l_suppkey);

  • Forward index scan, correctly uses index, avoids sorting

    explain analyze select * from lineitem l order by l.L_PARTKEY, l.L_SUPPKEY limit 1;
    -> Limit: 1 row(s)  (cost=0.00 rows=1) (actual time=0.065..0.065 rows=1 loops=1)
        -> Index scan on l using LINEITEM_FK2  (cost=0.00 rows=1) (actual time=0.063..0.063 rows=1 loops=1)

  • Reverse index scan, correctly uses index, avoids sorting

    explain analyze select * from lineitem l order by l.L_PARTKEY desc, l.L_SUPPKEY desc limit 1;
    -> Limit: 1 row(s)  (cost=0.00 rows=1) (actual time=0.033..0.033 rows=1 loops=1)
        -> Index scan on l using LINEITEM_FK2 (reverse)  (cost=0.00 rows=1) (actual time=0.032..0.032 rows=1 loops=1)

  • Unable to use index, full table scan

    explain analyze select * from lineitem l order by l.L_PARTKEY desc, l.L_SUPPKEY limit 1;
    -> Limit: 1 row(s)  (cost=33963.20 rows=1) (actual time=246.462..246.462 rows=1 loops=1)
        -> Sort: l.L_PARTKEY DESC, l.L_SUPPKEY, limit input to 1 row(s) per chunk  (cost=33963.20 rows=330097) (actual time=246.460..246.460 rows=1 loops=1)
            -> Table scan on l  (cost=33963.20 rows=330097) (actual time=0.109..186.665 rows=330122 loops=1)

PawSQL Auditing​

PawSQL provides a corresponding SQL audit warning for this case.

Default Warning Level​

  • Note

There are 3 warning levels in ascending order: Note < Warning < Error

Conditions​

  • Sort expressions are fields without operations
  • Sorted fields come from the same database table
  • Sort contains a mix of ASC (default) and DESC

Database Types and Versions​

  • MySQL
  • openGauss
  • Oracle
  • PostgreSQL
  • KingbaseES
  • MariaDB

About PawSQL​

PawSQL is dedicated to automatic and intelligent database performance optimization. The database types supported include MySQL, PostgreSQL, Oracle, and openGauss; and 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