Query optimiser is not using 'not null' constraint when 'order bynulls last' clause is used

SELECT     wos.notificationstatus,

           wos.unrepliedcount,

           wos.shownotestotech,

           wos.ownerid,

           wos.isfcr,

           aau.user_id,

           wo.workorderid         AS "WOID",

           wo.is_catalog_template AS "TemplateType",

           wo.title               AS "Title",

           wo.is_catalog_template,

           aau.first_name  AS "Requester",

           cd.categoryname AS "Category",

           ti.first_name   AS "Owner",

           wo.duebytime    AS "DueBy",

           wo.fr_duetime,

           wo.completedtime AS "CompletedTime",

           wo.respondedtime AS "RespondedTime",

           wo.resolvedtime  AS "ResolvedTime",

           qd.queuename     AS "Group",

           std.statusname   AS "Status",

           wo.createdtime   AS "CreatedDate",

           wos.isread,

           wos.hasattachment,

           wos.appr_statusid,

           wos.priorityid,

           wo.templateid AS "TemplateId",

           pd.priorityid,

           pd.priorityname  AS "Priority",

           pd.prioritycolor AS "PriorityColor",

           wos.isoverdue,

           wos.is_fr_overdue,

           wos.linkedworkorderid,

           wos.editing_status,

           wos.editorid,

           wos.linkedworkorderid,

           wo.isparent,

           sduser.isvipuser,

           sduser_onbehalfof.isvipuser AS "ONBEHALFOFVIP",

           wo.isparent,

           wos.statusid,

           sdorganization.name AS "Site",

           wo.workorderid      AS "RequestID"

FROM       workorder wo

left join  workorder_fields wof

ON         wo.workorderid=wof.workorderid

left join  servicecatalog_fields scf

ON         wo.workorderid=scf.workorderid

left join  wotoprojects wtp

ON         wo.workorderid=wtp.workorderid

left join  sitedefinition

ON         wo.siteid=sitedefinition.siteid

left join  sdorganization

ON         sitedefinition.siteid=sdorganization.org_id

inner join workorderstates wos

ON         wo.workorderid=wos.workorderid

left join  categorydefinition cd

ON         wos.categoryid=cd.categoryid

left join  aaauser ti

ON         wos.ownerid=ti.user_id

left join  aaauser aau

ON         wo.requesterid=aau.user_id

left join  prioritydefinition pd

ON         wos.priorityid=pd.priorityid

left join  statusdefinition std

ON         wos.statusid=std.statusid

left join  workorder_queue wo_queue

ON         wo.workorderid=wo_queue.workorderid

left join  queuedefinition qd

ON         wo_queue.queueid=qd.queueid

left join  departmentdefinition dpt

ON         wo.deptid=dpt.deptid

left join  leveldefinition lvd

ON         wos.levelid=lvd.levelid

left join  modedefinition mdd

ON         wo.modeid=mdd.modeid

left join  urgencydefinition urgdef

ON         wos.urgencyid=urgdef.urgencyid

left join  impactdefinition impdef

ON         wos.impactid=impdef.impactid

left join  requesttypedefinition rtdef

ON         wos.requesttypeid=rtdef.requesttypeid

left join  subcategorydefinition scd

ON         wos.subcategoryid=scd.subcategoryid

left join  itemdefinition icd

ON         wos.itemid=icd.itemid

left join  servicedefinition serdef

ON         wo.serviceid=serdef.serviceid

left join  aaauser cbau

ON         wo.createdbyid=cbau.user_id

left join  aaauser oboaau

ON         wo.oboid=oboaau.user_id

left join  sduser

ON         wo.requesterid=sduser.userid

left join  sduser sduser_onbehalfof

ON         wo.oboid=sduser_onbehalfof.userid

left join  workorder_fields

ON         wo.workorderid=workorder_fields.workorderid

WHERE      ((

                                 wos.statusid = 1)

           AND        (

                                 wo.isparent = TRUE))

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.

From the plan it looks like the second query is able to efficiently use the workorder_pk index ( The node 'Index Scan Backward using workorder_pk on workorder' returns 25 rows) whereas the first query is not able to use the index efficiently (more than 300k rows are returned from the same node).

The column workorderid is a PK column. The query optimizer should ideally know that there is no nulls in this column and in effect there is no difference between the two queries.

I tried the same in Postgres 10 and the slower query performs much better due to parallel sequential scans but still it is less efficient than the query without 'nulls last'.

I thought it would be best to raise this with the Postgres team.

Regards,

Nanda