From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ayub Khan <ayub.hp@gmail.com>
cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: query planner not using index, instead using squential scan
In-reply-to: 
 <CAHdeyEK6ipwa3TPfX=6HUZHf=4s4BsTgA8t45Ht0fs3QTJGB8Q@mail.gmail.com>
References: 
 <CAHdeyEK6ipwa3TPfX=6HUZHf=4s4BsTgA8t45Ht0fs3QTJGB8Q@mail.gmail.com>
Comments: In-reply-to Ayub Khan <ayub.hp@gmail.com>
	message dated "Sat, 05 Jun 2021 19:42:37 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <560772.1622921370.1@sss.pgh.pa.us>
Date: Sat, 05 Jun 2021 15:29:30 -0400
Message-ID: <560773.1622921370@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/560773.1622921370%40sss.pgh.pa.us>
Precedence: bulk

Ayub Khan <ayub.hp@gmail.com> writes:
> could someone clarify why the LEFT JOIN order_offer_map oom using
> (order_id) in the below query is using sequential scan instead of
> using index on order_id which is defined in order_offer_map table.

Probably because it estimates the hash join to restaurant_order is
faster than a nestloop join would be.  I think it's likely right.
You'd need very optimistic assumptions about the cost of an
individual index probe into order_offer_map to conclude that 156K
of them would be faster than the 476ms that are being spent here
to read order_offer_map and join it to the result of the
indexscan on restaurant_order.

If, indeed, that *is* faster on your hardware, you might want
to dial down random_page_cost to get more-relevant estimates.

			regards, tom lane