Postgres: Sorting NULL values causes performance degradation

Open on Drupal.org →

Created on 10 May 2023, over 1 year ago

Updated 16 November 2023, 11 months ago

I have a table with ~ 3 billion records.

Currently a simple SELECT query with ORDER BY primary key takes ~225ms. If I drop "NULLS LAST" from the pgsql\Select::orderBy it takes just 2 ms. On more complex queries the difference is much more noticeable.

The problem was already reported by @adam-vessey in https://www.drupal.org/project/drupal/issues/2443657#comment-14870439 🐛 PostgreSQL: Fix system\Tests\Entity\EntityQueryTest Fixed

PostgreSQL, and it seems like the changes made here on their own would interfere with index usage in PostgreSQL, at least with ordering operations since indexes in PostgreSQL: https://www.postgresql.org/docs/current/indexes-ordering.html

By default, B-tree indexes store their entries in ascending order with nulls last

So, since the "order by" statements no longer matches the indexes (as they're created by default), things are likely to fall back to sequential scans in situations where it otherwise would be fine.

Could/should we start adding "NULLS FIRST" to general index definitions that we generate for PostgreSQL, such that indexes will match the expectations of "order by" statements we pass it, and so facilitate their use?

🐛 Bug report

Status

Active

Version

11.0 🔥

Component

PostgreSQL driver →

Last updated 18 days ago

No maintainer

Created by

🇷🇺Russia Chi

Live updates comments and jobs are added and updated live.

Performance
It affects performance. It is often combined with the Needs profiling tag.

Incomplete comments

Sign in to follow issues

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

Issue created by @Chi
Comment over 1 year ago →
🇷🇺Russia Chi
A workaround that reverts 🐛 PostgreSQL: Fix system\Tests\Entity\EntityQueryTest Fixed .
Comment over 1 year ago →
🇳🇱Netherlands daffie
Thank you @Chi for reporting this. We should fix this.

To me this is the wrong solution. We should create the corresponding index with the same "NULLS LAST" option. See: https://www.postgresql.org/docs/9.6/sql-createindex.html
Comment over 1 year ago →
🇳🇱Netherlands daffie
@chi The table that you have the problems with, is that a table created by "core"? If not, do you have a by "core" created table that has the same problems?
Comment over 1 year ago →
🇷🇺Russia Chi
@daffie, it is a base table for custom entity type.
Open in Jenkins → Open on Drupal.org →
Environment: PHP 8.2 & pgsql-14.1
last update 12 months ago
29,632 pass, 3 fail
Open in Jenkins → Open on Drupal.org →
Environment: PHP 8.2 & sqlite-3.34
last update 12 months ago
29,678 pass
Comment 11 months ago →
🇲🇩Moldova Utilvideo Chisinau
Confirm patch working.

contrib.social Blog FAQ Discussions

Production build 0.71.5 2024