Why does ORDER BY NULLS LAST affect the query plan on a primary key?

Question

Using PostgreSQL 11, I have the following table with around 450 million rows:

postgres=> \d+ sales
                                                             Table "public.sales"
              Column               |            Type             |             Modifiers              | Storage  | Stats target | Description
-----------------------------------+-----------------------------+------------------------------------+----------+--------------+-------------
 created_terminal_id               | integer                     | not null                           | plain    |              |
 company_id                        | integer                     | not null                           | plain    |              |
 customer_id                       | integer                     |                                    | plain    |              |
 sale_no                           | character varying(20)       | not null                           | extended |              |
 sale_type                         | smallint                    | not null                           | plain    |              |
 source_type                       | smallint                    | not null                           | plain    |              |
 sale_date                         | timestamp without time zone | not null                           | plain    |              |
 paid_amount                       | numeric(18,4)               | not null default 0.0000            | main     |              |
 change_amount                     | numeric(18,4)               | not null default 0.0000            | main     |              |
 cashup_id                         | integer                     |                                    | plain    |              |
 staff_id                          | integer                     |                                    | plain    |              |
 payment_terminal_id               | integer                     | not null                           | plain    |              |
 site_id                           | integer                     | not null                           | plain    |              |
 sale_id                           | integer                     | not null                           | plain    |              |
 deleted                           | smallint                    | default 0                          | plain    |              |
 is_tax_on                         | smallint                    | not null default 1                 | plain    |              |
 props                             | text                        | not null                           | extended |              |
 modified_time                     | timestamp without time zone | not null default CURRENT_TIMESTAMP | plain    |              |
 sum_line_variation_ex_tax_price   | numeric(18,4)               | not null default 0.0000            | main     |              |
 sum_line_variation_inc_tax_price  | numeric(18,4)               | not null default 0.0000            | main     |              |
 sum_line_quantified_ex_tax_price  | numeric(18,4)               | not null default 0.0000            | main     |              |
 sum_line_quantified_inc_tax_price | numeric(18,4)               | not null default 0.0000            | main     |              |
 sum_line_subtotal_ex_tax_price    | numeric(18,4)               | not null default 0.0000            | main     |              |
 sum_line_subtotal_inc_tax_price   | numeric(18,4)               | not null default 0.0000            | main     |              |
 sum_line_total_ex_tax_price       | numeric(18,4)               | not null default 0.0000            | main     |              |
 sum_line_total_inc_tax_price      | numeric(18,4)               | not null default 0.0000            | main     |              |
 sum_line_cost_inc_tax_price       | numeric(18,4)               | not null default 0.0000            | main     |              |
 sum_line_cost_ex_tax_price        | numeric(18,4)               | not null default 0.0000            | main     |              |
 sum_payment_tip_price             | numeric(18,4)               | not null default 0.0000            | main     |              |
 order_variation_ex_tax_price      | numeric(18,4)               | not null default 0.0000            | main     |              |
 order_variation_inc_tax_price     | numeric(18,4)               | not null default 0.0000            | main     |              |
 order_total_ex_tax_price          | numeric(18,4)               | not null default 0.0000            | main     |              |
 order_total_inc_tax_price         | numeric(18,4)               | not null default 0.0000            | main     |              |
 order_tip_price                   | numeric(18,4)               | not null default 0.0000            | main     |              |
 order_variation_is_percent        | smallint                    | not null default 0                 | plain    |              |
 order_variation_percent           | numeric(18,4)               | not null default 1.0000            | main     |              |
 order_type                        | smallint                    |                                    | plain    |              |
 order_tip_is_percent              | smallint                    | not null default 0                 | plain    |              |
 order_tip_percent                 | numeric(18,4)               | not null default 1.0000            | main     |              |
 sale_date_id                      | integer                     | not null default 0                 | plain    |              |
 voided_sale_id                    | integer                     |                                    | plain    |              |
 voided_sale_date                  | timestamp without time zone |                                    | plain    |              |
 sale_date_utc                     | timestamp without time zone |                                    | plain    |              |
 foo                               | numeric(18,4)               |                                    | main     |              |
 bar                               | numeric(18,4)               | not null default 0                 | main     |              |
Indexes:
    "sales_pkey" PRIMARY KEY, btree (sale_id)
    "idx_unique_sale" UNIQUE CONSTRAINT, btree (created_terminal_id, sale_date, sale_no)
    "idx_sale_cashup_id" btree (cashup_id)
    "idx_sale_customer_id" btree (customer_id)
    "idx_sale_modified_time" btree (modified_time)
    "idx_sale_payment_terminal_id" btree (payment_terminal_id)
    "idx_sale_site_date" btree (sale_date)
    "idx_sale_site_id" btree (site_id)
    "idx_sale_staff_id" btree (staff_id)
    "sales_company_id" btree (company_id)
    "sales_sale_date_id" btree (sale_date_id)
Has OIDs: no

Performing the following query takes around 35 minutes:

postgres=> EXPLAIN
postgres-> SELECT sales.sale_id as numeric_id, sales.site_id, sales.created_terminal_id  as terminal_id, sales.props as props, sales.order_total_inc_tax_price as SaleAmount, sales.staff_id as staff_id, sales.paid_amount as PaidAmount, (sales.order_total_inc_tax_price - sales.order_total_ex_tax_price) as taxAmount, sales.sale_date as SaleDate, sales.sale_no as SaleNo, sales.voided_sale_id as LinkedSaleNo, sales.voided_sale_date as LinkedSaleDate, sales.sale_type as sale_type, sales.deleted
postgres-> FROM sales
postgres-> WHERE sales.deleted = 0
postgres->   AND sales.site_id = 72620
postgres->   AND order_total_inc_tax_price < 40
postgres->   AND sale_date > '2019-03-08'
postgres-> ORDER BY sale_id DESC
postgres-> LIMIT 50;
                                                                                    QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------
 Limit  (cost=1000.60..76779.68 rows=50 width=189)
   ->  Gather Merge  (cost=1000.60..29806430.01 rows=19666 width=189)
         Workers Planned: 2
         ->  Parallel Index Scan Backward using sales_pkey on sales  (cost=0.57..29803160.04 rows=8194 width=189)
               Filter: ((order_total_inc_tax_price < '40'::numeric) AND (sale_date > '2019-03-08 00:00:00'::timestamp without time zone) AND (del
eted = 0) AND (site_id = 72620))
(5 rows)

However, if I change ORDER BY sale_id DESC NULLS LAST I get a radically different plan and enormous speed increase (query takes just like a few seconds):

postgres=> EXPLAIN
postgres-> SELECT sales.sale_id as numeric_id, sales.site_id, sales.created_terminal_id  as terminal_id, sales.props as props, sales.order_total_inc_tax_price as SaleAmount, sales.staff_id as staff_id, sales.paid_amount as PaidAmount, (sales.order_total_inc_tax_price - sales.order_total_ex_tax_price) as taxAmount, sales.sale_date as SaleDate, sales.sale_no as SaleNo, sales.voided_sale_id as LinkedSaleNo, sales.voided_sale_date as LinkedSaleDate, sales.sale_type as sale_type, sales.deleted
postgres-> FROM sales
postgres-> WHERE sales.deleted = 0
postgres->   AND sales.site_id = 72620
postgres->   AND order_total_inc_tax_price < 40
postgres->   AND sale_date > '2019-03-08'
postgres-> ORDER BY sale_id DESC NULLS LAST
postgres-> LIMIT 50;
                                                                         QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------
-----------
 Limit  (cost=216622.14..216622.26 rows=50 width=189)
   ->  Sort  (cost=216622.14..216671.30 rows=19666 width=189)
         Sort Key: sale_id DESC NULLS LAST
         ->  Index Scan using idx_sale_site_id on sales  (cost=0.57..215968.85 rows=19666 width=189)
               Index Cond: (site_id = 72620)
               Filter: ((order_total_inc_tax_price < '40'::numeric) AND (sale_date > '2019-03-08 00:00:00'::timestamp without time zone) AND (del
eted = 0))
(6 rows)

I am sorting by the PRIMARY KEY, which cannot contain NULLs, so why is it that the query planner makes this choice?

It's worth noting that this server is just for benchmarking, there is no other load on the database. Also ANALYZE was run after loading in the data.

score 8 · Accepted Answer · answered Dec 04 '19 at 09:19

An index can only be used to process an ORDER BY clause without sorting if the index is in the same order as specified by ORDER BY.

Now your index is (by default) sorted ASC NULLS LAST, and since an index can be scanned in both directions, it can support both ORDER BY sale_id ASC NULLS LAST and ORDER BY sale_id DESC NULLS FIRST. But since the ordering is different, it cannot support ORDER BY sale_id DESC NULLS LAST.

The planner does not take the NOT NULL of the column definition into account. This is determined in build_index_pathkeys in src/backend/optimizer/path/pathkeys.c:

    if (ScanDirectionIsBackward(scandir))
    {
        reverse_sort = !index->reverse_sort[i];
        nulls_first = !index->nulls_first[i];
    }
    else
    {
        reverse_sort = index->reverse_sort[i];
        nulls_first = index->nulls_first[i];
    }

    /*
     * OK, try to make a canonical pathkey for this sort key.  Note we're
     * underneath any outer joins, so nullable_relids should be NULL.
     */
    cpathkey = make_pathkey_from_sortinfo(root,
                                          indexkey,
                                          NULL,
                                          index->sortopfamily[i],
                                          index->opcintype[i],
                                          index->indexcollations[i],
                                          reverse_sort,
                                          nulls_first,
                                          0,
                                          index->rel->relids,
                                          false);

I don't know how easy it would be to take nullability into account here, but it isn't done at the moment.

Perhaps you could suggest that to the pgsql-hackers mailing list.

Why does ORDER BY NULLS LAST affect the query plan on a primary key?

1 Answers1

Linked