From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Nandakumar M <m.nanda92@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used
Date: Fri, 02 Feb 2018 10:36:40 +0100
Message-ID: <1517564200.2452.10.camel@cybertec.at> (raw)
In-Reply-To: <CANcFUu63MDBXZrnGoBML551z2aA0KC3erWSeH=E-2nmyOF3rYQ@mail.gmail.com>
References: <CANcFUu63MDBXZrnGoBML551z2aA0KC3erWSeH=E-2nmyOF3rYQ@mail.gmail.com>
On Thu, 2018-02-01 at 20:00 +0530, Nandakumar M wrote:
> Hi,
>
> I am using Postgres version 9.4.4 on a Mac machine.
> I have 2 queries that differ only in the order by clause.
> One of it has 'nulls last' and the other one does not have it.
> The performance difference between the two is considerable.
>
> The slower of the two queries is
>
> SELECT [...]
> FROM workorder wo
> left join workorder_fields wof
> ON wo.workorderid=wof.workorderid
> left join servicecatalog_fields scf
> ON wo.workorderid=scf.workorderid
[...]
> ORDER BY 7 DESC nulls last limit 25
>
>
>
> On removing 'nulls last' from the order by clause the query becomes very fast.
> I have attached the query plan for both the queries.
In the above case, the optimizer does not know that it will get the rows
in the correct order: indexes are sorted ASC NULLS LAST by default,
so a backwards index scan will produce the results NULLS FIRST,
which is the default for ORDER BY ... DESC.
If you want the nulls last, PostgreSQL has to retrieve *all* the rows and sort
them rather than using the first 25 results it gets by scanning then indexes.
To have the above query perform fast, add additional indexes with either
ASC NULLS FIRST or DESC NULLS LAST for all used keys.
Yours,
Laurenz Albe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, m.nanda92@gmail.com
Subject: Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used
In-Reply-To: <1517564200.2452.10.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox