From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | David Rowley <dgrowleyml(at)gmail(dot)com> |
Cc: | Jean Landercy - BEEODIVERSITY <jean(dot)landercy(at)beeodiversity(dot)com>, "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Sudden database error with COUNT(*) making Query Planner crashes: variable not found in subplan target list |
Date: | 2022-06-07 20:57:00 |
Message-ID: | 1331598.1654635420@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> On Wed, 8 Jun 2022 at 07:55, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2022-06-07 21:07:20 | Re: Collation version tracking for macOS |
Previous Message | Peter Geoghegan | 2022-06-07 20:51:50 | Re: Collation version tracking for macOS |