MIME-Version: 1.0
References: 
 <CAKfDxxzqxm0gS44+4aUDejLq_iszqLJwR+EaM4x2pojrkV58eg@mail.gmail.com>
In-Reply-To: 
 <CAKfDxxzqxm0gS44+4aUDejLq_iszqLJwR+EaM4x2pojrkV58eg@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Thu, 19 Dec 2024 13:38:57 -0500
Message-ID: 
 <CAKAnmmL327t5MRJ2uonQ3JB5bLegRbfa-WtpaTNBnbJ1iVef5Q@mail.gmail.com>
Subject: Re: Why a bitmap scan in this case?
To: Jon Zeppieri <zeppieri@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000f7e1a20629a3d9a9"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKAnmmL327t5MRJ2uonQ3JB5bLegRbfa-WtpaTNBnbJ1iVef5Q%40mail.gmail.com>
Precedence: bulk

--000000000000f7e1a20629a3d9a9
Content-Type: text/plain; charset="UTF-8"

>
> Why wouldn't it do an index (or, really, an index only) scan in this case


Well, it did do an index scan (and a bitmap scan is a pretty good solution
here), but as to why no indexonly scan, there is probably not enough
assurance that it won't have to hit the heap heavily anyway. Try doing a
SET enable_bitmapscan=0; and re-run with EXPLAIN ANALYZE. If you see a
large number of "Heap Fetches", that could be why. Vacuum the table and try
again after doing SET enable_bitmapscan=1;

Cheers,
Greg

--000000000000f7e1a20629a3d9a9
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div class=3D"gmail_quote gmail_quote_container"><blockquo=
te class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px =
solid rgb(204,204,204);padding-left:1ex">Why wouldn&#39;t it do an index (o=
r, really, an index only) scan in this case</blockquote><div><br></div><div=
>Well, it did do an index scan (and a bitmap scan is a pretty=C2=A0good sol=
ution here), but as to why no indexonly scan, there is probably not enough =
assurance that it won&#39;t have to hit the heap heavily anyway. Try doing =
a SET enable_bitmapscan=3D0; and re-run with EXPLAIN ANALYZE. If you see a =
large number of &quot;Heap Fetches&quot;, that could be why. Vacuum the tab=
le and try again after doing SET enable_bitmapscan=3D1;</div><div><br></div=
><div>Cheers,</div><div>Greg</div><div><br></div><div><br></div><div><br></=
div></div></div>

--000000000000f7e1a20629a3d9a9--