Re: Sudden database error with COUNT(*) making Query Planner crashes: variable not found in subplan target list

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