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

On Wed, 8 Jun 2022 at 07:55, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> David Rowley <dgrowleyml@gmail.com> writes:
> > On Tue, 7 Jun 2022 at 19:58, Jean Landercy - BEEODIVERSITY
> > <jean.landercy@beeodiversity.com> wrote:
> >> Here is the detail of the table (I have anonymized it on SO, this is its real name):
> >> "logistic_site_location_54ae0166_id" gist (location)
> > I imagine this is due to the planner choosing an index-only scan on
> > the above index. A similar problem was reported in [1].
>
> The other gist index could also be the problem.  It seems odd though
> that the planner would favor either index for this purpose over the btree
> indexes on scalar columns, which you'd think would be a lot smaller.
> 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)

# drop index logistic_site_geom_105a08da_id;
# explain select count(*) from logistic_site;
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Aggregate  (cost=13.93..13.94 rows=1 width=8)
   ->  Bitmap Heap Scan on logistic_site  (cost=9.26..13.39 rows=213 width=0)
         ->  Bitmap Index Scan on logistic_site_key_2e791173_like
(cost=0.00..9.21 rows=213 width=0)
(3 rows)

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.

David