pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery optimiser is not using 'not null' constraint when 'order by nulls last' clause is used
7+ messages / 4 participants
[nested] [flat]
* Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used
@ 2018-02-01 14:30 Nandakumar M <m.nanda92@gmail.com>
2018-02-02 09:36 ` Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 7+ messages in thread
From: Nandakumar M @ 2018-02-01 14:30 UTC (permalink / raw)
To: pgsql-performance
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 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used
2018-02-01 14:30 Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Nandakumar M <m.nanda92@gmail.com>
@ 2018-02-02 09:36 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-02-02 14:04 ` Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Nandakumar M <m.nanda92@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2018-02-02 09:36 UTC (permalink / raw)
To: Nandakumar M <m.nanda92@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used
2018-02-01 14:30 Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Nandakumar M <m.nanda92@gmail.com>
2018-02-02 09:36 ` Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Laurenz Albe <laurenz.albe@cybertec.at>
@ 2018-02-02 14:04 ` Nandakumar M <m.nanda92@gmail.com>
2018-02-02 15:00 ` Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Nandakumar M @ 2018-02-02 14:04 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-performance
Hi,
On 2 Feb 2018 15:06, "Laurenz Albe" <laurenz.albe@cybertec.at> wrote:
>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.
The order by column has a not null constraint on it and so nulls last or
first shouldn't make any difference.
>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.
For now this is exactly what I have done. But it is in effect a duplicate
index on a PK column and I would be happy not to create it in the first
place.
Regards
Nanda
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used
2018-02-01 14:30 Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Nandakumar M <m.nanda92@gmail.com>
2018-02-02 09:36 ` Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Laurenz Albe <laurenz.albe@cybertec.at>
2018-02-02 14:04 ` Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Nandakumar M <m.nanda92@gmail.com>
@ 2018-02-02 15:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-02-02 15:49 ` Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Nandakumar M <m.nanda92@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2018-02-02 15:00 UTC (permalink / raw)
To: Nandakumar M <m.nanda92@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance
Nandakumar M <m.nanda92@gmail.com> writes:
> The order by column has a not null constraint on it and so nulls last or
> first shouldn't make any difference.
The planner does not consider this and it doesn't really seem like
something worth expending cycles on. If you know that there won't be
nulls in the column, why are you insisting on specifying a nondefault
value of NULLS FIRST/LAST in the query?
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used
2018-02-01 14:30 Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Nandakumar M <m.nanda92@gmail.com>
2018-02-02 09:36 ` Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Laurenz Albe <laurenz.albe@cybertec.at>
2018-02-02 14:04 ` Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Nandakumar M <m.nanda92@gmail.com>
2018-02-02 15:00 ` Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Tom Lane <tgl@sss.pgh.pa.us>
@ 2018-02-02 15:49 ` Nandakumar M <m.nanda92@gmail.com>
2018-02-02 15:58 ` Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Nandakumar M @ 2018-02-02 15:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance
Hi,
On Fri, Feb 2, 2018 at 8:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> The planner does not consider this and it doesn't really seem like
> something worth expending cycles on. If you know that there won't be
> nulls in the column, why are you insisting on specifying a nondefault
> value of NULLS FIRST/LAST in the query?
The query is generated by a framework that adds 'nulls last' to all
order by clause.
This is done apparently to provide common behaviour in our application
irrespective of the database that is used.
SQL server treats nulls as lesser than non null values which is
opposite to what Postgres does.
For any indexes that we create manually, we can do a
--> create index on table_name(column_name nulls first);
But, for the PK column we are not in control of the index that is created.
Regards,
Nanda
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used
2018-02-01 14:30 Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Nandakumar M <m.nanda92@gmail.com>
2018-02-02 09:36 ` Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Laurenz Albe <laurenz.albe@cybertec.at>
2018-02-02 14:04 ` Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Nandakumar M <m.nanda92@gmail.com>
2018-02-02 15:00 ` Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Tom Lane <tgl@sss.pgh.pa.us>
2018-02-02 15:49 ` Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Nandakumar M <m.nanda92@gmail.com>
@ 2018-02-02 15:58 ` David G. Johnston <david.g.johnston@gmail.com>
2018-02-02 17:01 ` Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Nandakumar M <m.nanda92@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2018-02-02 15:58 UTC (permalink / raw)
To: Nandakumar M <m.nanda92@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance
On Fri, Feb 2, 2018 at 8:49 AM, Nandakumar M <m.nanda92@gmail.com> wrote:
> But, for the PK column we are not in control of the index that is created.
>
You probably can (I assume the nulls aspect of the index doesn't prevent
PK usage), but you must add the PK to the table after creating the index
and not let the system auto-generate the index for you.
https://www.postgresql.org/docs/10/static/sql-altertable.html
ALTER TABLE name ADD PRIMARY KEY USING INDEX index_name;
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used
2018-02-01 14:30 Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Nandakumar M <m.nanda92@gmail.com>
2018-02-02 09:36 ` Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Laurenz Albe <laurenz.albe@cybertec.at>
2018-02-02 14:04 ` Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Nandakumar M <m.nanda92@gmail.com>
2018-02-02 15:00 ` Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Tom Lane <tgl@sss.pgh.pa.us>
2018-02-02 15:49 ` Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Nandakumar M <m.nanda92@gmail.com>
2018-02-02 15:58 ` Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used David G. Johnston <david.g.johnston@gmail.com>
@ 2018-02-02 17:01 ` Nandakumar M <m.nanda92@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Nandakumar M @ 2018-02-02 17:01 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance
Hi,
On Fri, Feb 2, 2018 at 9:28 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> You probably can (I assume the nulls aspect of the index doesn't prevent PK
> usage), but you must add the PK to the table after creating the index and
> not let the system auto-generate the index for you.
>
> https://www.postgresql.org/docs/10/static/sql-altertable.html
>
> ALTER TABLE name ADD PRIMARY KEY USING INDEX index_name;
>
I missed to notice this in the docs. Thank you David for pointing it out.
Regards,
Nanda
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2018-02-02 17:01 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-02-01 14:30 Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used Nandakumar M <m.nanda92@gmail.com>
2018-02-02 09:36 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-02-02 14:04 ` Nandakumar M <m.nanda92@gmail.com>
2018-02-02 15:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-02-02 15:49 ` Nandakumar M <m.nanda92@gmail.com>
2018-02-02 15:58 ` David G. Johnston <david.g.johnston@gmail.com>
2018-02-02 17:01 ` Nandakumar M <m.nanda92@gmail.com>
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