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

From Tom Lane
Subject Re: Sudden database error with COUNT(*) making Query Planner crashes: variable not found in subplan target list
Date
Msg-id 1339803.1654637046@sss.pgh.pa.us
Whole thread Raw
In response to Re: Sudden database error with COUNT(*) making Query Planner crashes: variable not found in subplan target list  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
I wrote:
> 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.

Ah, false alarm.  I can reproduce your results if I stick an ANALYZE
between the first and second EXPLAIN.  So probably your change in
estimated rowcount and hence cost can be explained by an auto-analyze
coming along at just the right time.

Also, if I fill the geom and location columns with non-null data,
the planner stops preferring those indexes.

So now I'm guessing that the OP's data *was* mostly null, and the
planner preferred the gist indexes because they were smallest,
and then tripped over the nonreturnable-column bug.

            regards, tom lane



pgsql-hackers by date:

Previous
From: Jacob Champion
Date:
Subject: Re: [PoC] Let libpq reject unexpected authentication requests
Next
From: Bruce Momjian
Date:
Subject: Re: Collation version tracking for macOS