From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ronuk Raval <ronuk.raval@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: Paul McGarry <paul@paulmcgarry.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Odd Choice of seq scan
Date: Fri, 02 Dec 2022 01:16:10 -0500
Message-ID: <1877071.1669961770@sss.pgh.pa.us> (raw)
In-Reply-To: <CAPhHnhr7NZdR7J=4H4ndsfXjPP6-V8VDtiUrcW_=iWcrhQXGYg@mail.gmail.com>
References: <CAPrE0Sahe_J1R3iN1kfyFR1j7prXE-SaqH5rK5JG-u2ToAzUJA@mail.gmail.com>
<20221202012124.GA14156@telsasoft.com>
<CAPhHnhr7NZdR7J=4H4ndsfXjPP6-V8VDtiUrcW_=iWcrhQXGYg@mail.gmail.com>
Ronuk Raval <ronuk.raval@gmail.com> writes:
> We've been working around the problem by rewriting queries to use UNION instead.
Yeah, that. The real issue here is that the seqscan and indexscan plans
both suck, because they will both run that sub-select for every row
in the table. The index-only plan might fetch fewer blocks along the
way, because it only has to read the index not the table proper ...
but that's only true if the table's pages are mostly marked all-visible.
(My bet about the plan instability is that the planner might choose
differently depending on how much of the table it believes is
all-visible.) That only helps a bit, though.
What you really want to have happen, assuming there are not too many
interesting orderid values, is to do a point indexscan for each one
of them. Currently the planner won't think of that by itself when
faced with OR'd conditions in WHERE. You have to help it along with
UNION or some similar locution.
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, ronuk.raval@gmail.com, pryzby@telsasoft.com, paul@paulmcgarry.com, pgsql-performance@lists.postgresql.org
Subject: Re: Odd Choice of seq scan
In-Reply-To: <1877071.1669961770@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