<div dir="auto">Hi,<div dir="auto"><br></div><div dir="auto">Bitmap scans in the plans are usually a sign of a system's random_page_cost being too high or/and work_mem being too low.</div><div dir="auto"><br></div><div dir="auto">In general, I'd say it is more about incorrect costing of Bitmap scans in postgres itself - there is an assumption buried deep into it that bitmap scan with per-page bitmap and with per-tuple bitmap will cost similar, while actually it should be st_within_cost*n_tuples_per_page times higher. This can sometimes be fought by higher work_mem but if someone gets this addressed upstream this will be nice.</div><div dir="auto"><br></div><div dir="auto">For costing of postgis functions, there was a research that measuret real cost, unlike the rest of postgres where costs are guesswork: <a href="https://github.com/postgis/postgis/pull/104">https://github.com/postgis/postgis/pull/104</a> </div><div dir="auto"><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">сб, 24 вер 2022, 23:13 карыстальнік Tomas Vondra <<a href="mailto:tomas.vondra@2ndquadrant.com">tomas.vondra@2ndquadrant.com</a>> напісаў:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi,<br>
<br>
We've been investigating a customer issue, where queries got much slower<br>
due to a query plan change after upgrading from PG 9.5 with PostGIS 2.5<br>
to PG 12 with PostGIS 3.1.<br>
<br>
The exact query and query plan is not very complicated or important -<br>
it's a simple non-join query, with st_within() in a WHERE condition (and<br>
a LIMIT on top), and the cardinality estimates are pretty spot on.<br>
<br>
The interesting bit is that on 9.5 the GIST index is accessed like this:<br>
<br>
   ->  Index Scan using my_gist__index on public.my_table sc<br>
       (cost=0.42..168630.72 rows=412 width=64) ...<br>
<br>
while on PG12 the index scan is costed like this:<br>
<br>
   ->  Index Scan using my_gist_index on public.my_table sc<br>
       (cost=0.42..1165450.26 rows=322 width=72) ...<br>
<br>
Yup, that's a 1M difference in cost, because in cost_index we do this:<br>
<br>
    cpu_run_cost += cpu_per_tuple * tuples_fetched;<br>
<br>
with tuples_fetched being ~40k rows, and the cpu_per_tuple jumps from<br>
0.29 to more than 25.0. This happens because of st_within() in the WHERE<br>
condition, and procost getting increased from 100 to 10000 in commit<br>
<br>
  commit 3ddc11483b2266d3a914ecfdeb3e2a343e0f6dd4<br>
  Author:     Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca" target="_blank" rel="noreferrer">pramsey@cleverelephant.ca</a>> []<br>
  AuthorDate: Fri Mar 8 21:48:08 2019 +0000<br>
  CommitDate: Fri Mar 8 21:48:08 2019 +0000<br>
<br>
      Add costs to functions in the form of macros:<br>
      _COST_LOW, _COST_MEDIUM, _COST_HIGH<br>
      Macros are set in <a href="http://sqldefines.h.in" rel="noreferrer noreferrer" target="_blank">sqldefines.h.in</a> and are switched<br>
      on PgSQL version. Prior to PG12, costs should be<br>
      small or nonexistent, to avoid SQL inlining issues.<br>
      After Pg12, costs can be higher to hopefully<br>
      force more parallelism in plans.<br>
      Closes #4344<br>
<br>
This however makes the index scan too expensive, and makes the optimizer<br>
to pick parallel query with a bitmap index scan instead. Which runs in<br>
about 1000 ms, while the original plan (after pushing the right enable_<br>
GUCs) runs in ~10ms.<br>
<br>
I agree the idea of commit 3ddc11483b might be right in principle, but I<br>
wonder if it went a bit too far - maybe jump from 100 to 10000 is a bit<br>
too much? I tried searching the postgis-devel archives for explanation<br>
why this is the right increase, but I haven't found anything.<br>
<br>
Of course, I can do<br>
<br>
    ALTER FUNCTION  st_within(geometry,geometry) COST 100<br>
<br>
to revert to the original cost, but doing that on every PostGIS install<br>
seems a bit impractical. Furthermore, there seem to be about ~120<br>
functions with this cost, and I'm not sure I want to tweak all of them.<br>
<br>
Opinions?<br>
<br>
<br>
regards<br>
<br>
-- <br>
Tomas Vondra<br>
EnterpriseDB: <a href="http://www.enterprisedb.com" rel="noreferrer noreferrer" target="_blank">http://www.enterprisedb.com</a><br>
The Enterprise PostgreSQL Company<br>
_______________________________________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank" rel="noreferrer">postgis-devel@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><br>
</blockquote></div>