Contrib.social

Postgres: Sorting NULL values causes performance degradation

Open on Drupal.org →
Created on 10 May 2023, over 1 year ago
Updated 16 November 2023, about 1 year 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 6 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.

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.

contrib.social Blog FAQ Discussions
Production build 0.71.5 2024