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
Date: Sat, 05 Jun 2021 15:29:30 -0400
Message-ID: <560773.1622921370@sss.pgh.pa.us> (raw)
In-Reply-To: <CAHdeyEK6ipwa3TPfX=6HUZHf=4s4BsTgA8t45Ht0fs3QTJGB8Q@mail.gmail.com>
References: <CAHdeyEK6ipwa3TPfX=6HUZHf=4s4BsTgA8t45Ht0fs3QTJGB8Q@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, ayub.hp@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: query planner not using index, instead using squential scan
In-Reply-To: <560773.1622921370@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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