From: Tom Lane <tgl@sss.pgh.pa.us>
To: Nandakumar M <m.nanda92@gmail.com>
cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: Need help with optimising simple query
In-reply-to: 
 <CANcFUu5a9W_MORQa5Tv9vsofN_w_irU9-OBQtN34YmL7p4gP_g@mail.gmail.com>
References: 
 <CANcFUu5a9W_MORQa5Tv9vsofN_w_irU9-OBQtN34YmL7p4gP_g@mail.gmail.com>
Comments: In-reply-to Nandakumar M <m.nanda92@gmail.com>
	message dated "Mon, 09 Jul 2018 17:17:23 +0530"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <9714.1531146227.1@sss.pgh.pa.us>
Date: Mon, 09 Jul 2018 10:23:47 -0400
Message-ID: <9715.1531146227@sss.pgh.pa.us>
Precedence: bulk

Nandakumar M <m.nanda92@gmail.com> writes:
> I am having a query that has an order by and a limit clause. The
> column on which I am doing order by is indexed (default b tree index).
> However the index is not being used. On tweaking the query a bit I
> found that when I use left join index is not used whereas when I use
> inner join the index is used.

The reason the index isn't being used is that the sort order the query
requests isn't the same as the order provided by the index.  Here:

> performance_test=# explain analyze select * from parent left join
> child on parent.child_id = child.id order by child.name limit 10;

you're asking to sort by a column that will include null values for
child.name anywhere that there's a parent row without a match for
child_id.  Those rows aren't even represented in the index on child.name,
much less placed in the right order.

			regards, tom lane