From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ronuk Raval <ronuk.raval@gmail.com>
cc: Justin Pryzby <pryzby@telsasoft.com>, Paul McGarry <paul@paulmcgarry.com>,
        pgsql-performance@lists.postgresql.org
Subject: Re: Odd Choice of seq scan
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>
Comments: In-reply-to Ronuk Raval <ronuk.raval@gmail.com>
	message dated "Fri, 02 Dec 2022 00:37:50 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1877070.1669961770.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 02 Dec 2022 01:16:10 -0500
Message-ID: <1877071.1669961770@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1877071.1669961770%40sss.pgh.pa.us>
Precedence: bulk

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