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