Re: distinguish index cost component from table component

--00000000000088f761059b3d3299
Content-Type: text/plain; charset="UTF-8"

On Fri, Jan 3, 2020 at 9:14 AM Justin Pryzby <[email protected]> wrote:

> Is it possible to tell what component of the cost estimate of an index
> scan is
> from the index reads vs heap ?
>

Not that I have found, other than through sprinkling elog statements
throughout the costing code.  Which is horrible, because then you get
estimates for all the considered but rejected index scans as well, but
without the context to know what they are for.  So it only works for toy
queries where there are few possible indexes to consider.

It would help to be able to set enable_bitmapscan=FORCE (to make all index
> scans go through a bitmap).


Doesn't enable_indexscan=off accomplish this already?  It is possible but
not terribly likely to switch from index to seq, rather than from index to
bitmap.  (Unless the index scan was being used to obtain an ordered result,
but a hypothetical enable_bitmapscan=FORCE can't fix that).

Of course this doesn't really answer your question, as the
separately-reported costs of a bitmap heap and bitmap index scan are
unlikely to match what the costs would be of a regular index scan, if they
were reported separately.

Or maybe explain should report it.
>

I wouldn't be optimistic about getting such a backwards-incompatible change
accepted (plus it would surely add some small accounting overhead, which
again would probably not be acceptable). But if you do enough tuning work,
perhaps it would be worth carrying an out-of-tree patch to implement that.
I wouldn't be so interested in writing such a patch, but would be
interested in using one were it available somewhere.

Cheers,

Jeff

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

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Jan 3, 2020 at 9:14 AM Justin Pry=
zby &lt;<a href=3D"mailto:[email protected]">[email protected]</a>&gt=
; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quo=
te" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204=
);padding-left:1ex">Is it possible to tell what component of the cost estim=
ate of an index scan is<br>
from the index reads vs heap ?<br></blockquote><div><br></div><div>Not that=
 I have found, other than through sprinkling elog statements throughout the=
 costing code.=C2=A0 Which is horrible, because then you get estimates for =
all the considered but rejected index scans as well, but without the contex=
t to know what they are for.=C2=A0 So it only works for toy queries where t=
here are few possible indexes to consider.</div><div><br></div><blockquote =
class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px sol=
id rgb(204,204,204);padding-left:1ex">
It would help to be able to set enable_bitmapscan=3DFORCE (to make all inde=
x<br>
scans go through a bitmap).=C2=A0 </blockquote><div><br></div><div>Doesn&#3=
9;t enable_indexscan=3Doff accomplish=C2=A0this already?=C2=A0 It is possib=
le=C2=A0but not terribly likely to switch from index to seq, rather than fr=
om index to bitmap.=C2=A0 (Unless the index scan was being used to obtain a=
n ordered result, but a hypothetical=20

enable_bitmapscan=3DFORCE can&#39;t fix that).</div><div><br></div><div>Of =
course this doesn&#39;t really answer your question, as the separately-repo=
rted costs of a bitmap heap and bitmap index scan are unlikely=C2=A0to matc=
h what the costs would be of a regular index scan, if they were reported se=
parately.</div><div><br></div><blockquote class=3D"gmail_quote" style=3D"ma=
rgin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:=
1ex">
Or maybe explain should report it.<br></blockquote><div><br></div><div>I wo=
uldn&#39;t be optimistic=C2=A0about getting=C2=A0such a backwards-incompati=
ble change accepted=20

(plus it would surely add some small accounting overhead, which again would=
 probably not be acceptable). But if you do enough tuning work, perhaps it =
would be worth carrying an out-of-tree patch to implement that.=C2=A0 I wou=
ldn&#39;t be so interested in writing such a patch, but would be interested=
 in using one were it available somewhere.</div><div><br></div><div>Cheers,=
</div><div><br></div><div>Jeff</div></div></div>

--00000000000088f761059b3d3299--