Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 655271337B69 for ; Wed, 23 Feb 2011 17:39:10 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 59330-06 for ; Wed, 23 Feb 2011 21:39:04 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id ADC531337B6E for ; Wed, 23 Feb 2011 17:39:02 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p1NLbYxG007346; Wed, 23 Feb 2011 16:37:34 -0500 (EST) To: Merlin Moncure cc: Mathieu De Zutter , pgsql-performance Subject: Re: NULLS LAST performance In-reply-to: References: Comments: In-reply-to Merlin Moncure message dated "Wed, 23 Feb 2011 13:48:03 -0600" Date: Wed, 23 Feb 2011 16:37:34 -0500 Message-ID: <7345.1298497054@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201102/461 X-Sequence-Number: 42599 Merlin Moncure writes: > you can always do this: > create index performance_creation_date_desc_idx on > performance(creation_date desc nulls last); > which will index optimize your sql. Interesting that 'null last' > fools disallows index usage even when the index was created with > nullls last as the default. The problem is that his query needs to scan the index in DESC order, which means it's effectively NULLS FIRST, which doesn't match the requested sort order. regards, tom lane