| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | alexander(dot)berezin3000(at)gmail(dot)com |
| Cc: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Subject: | Re: BUG #18477: A specific SQL query with "ORDER BY ... NULLS FIRST" is performing poorly if an ordering column is n |
| Date: | 2024-05-23 18:26:10 |
| Message-ID: | 375311.1716488770@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> A specific SQL query with "ORDER BY ... NULLS FIRST" is performing poorly if
> an ordering column is not nullable.
The reason it's performing poorly is that
ORDER BY updated_at NULLS FIRST
is not compatible with the sort order of your index (which is,
by default, NULLS LAST). So the query has to be done with an
explicit sort, which requires reading the whole table.
I know you are going to say that it shouldn't matter as long as the
column is marked NOT NULL, but too bad: it does. This is not a bug,
and it's not something we're likely to expend a great deal of sweat
on improving. If you know the column is null-free, why are you
writing NULLS FIRST? If you have a good reason to write NULLS FIRST,
why not declare the index to match?
regards, tom lane
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Thomas Munro | 2024-05-24 00:45:14 | Re: BUG #18334: Segfault when running a query with parallel workers |
| Previous Message | PG Bug reporting form | 2024-05-23 17:34:12 | BUG #18477: A specific SQL query with "ORDER BY ... NULLS FIRST" is performing poorly if an ordering column is n |