David Rowley <dgrowleyml@gmail.com> writes:
> On Wed, 8 Jun 2022 at 07:55, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I wonder if there is some quirk in gist cost estimation that makes it
>> improperly claim to be cheaper than btree scans.
> I installed PostGIS 3.1.1 and mocked this up with the attached.
> Looking at the plans, I see:
> # explain select count(*) from logistic_site;
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------
> Aggregate (cost=20.18..20.19 rows=1 width=8)
> -> Bitmap Heap Scan on logistic_site (cost=5.92..19.32 rows=340 width=0)
> -> Bitmap Index Scan on logistic_site_location_54ae0166_id
> (cost=0.00..5.84 rows=340 width=0)
> (3 rows)
> # drop index logistic_site_location_54ae0166_id;
> # explain select count(*) from logistic_site;
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------
> Aggregate (cost=9.92..9.93 rows=1 width=8)
> -> Bitmap Heap Scan on logistic_site (cost=5.26..9.39 rows=213 width=0)
> -> Bitmap Index Scan on logistic_site_geom_105a08da_id
> (cost=0.00..5.20 rows=213 width=0)
> (3 rows)
That ... is pretty quirky already. How did it prefer a scan with cost
19.32 over one with cost 9.39? Seems like we've got a bug here somewhere.
The change in estimated rowcount is rather broken, too.
> So it does appear that the location index is being chosen, at least
> with the data that I inserted. Those gist indexes are costing quite a
> bit cheaper than the cheapest btree index.
It looks like the data you inserted for the geometry columns was uniformly
NULL, which perhaps would result in a very small gist index. So maybe
for this test data the choice isn't so odd. Seems unlikely that that'd
be true of the OP's production data, though.
regards, tom lane