maria-discuss team mailing list archive

[David Strauss <david@xxxxxxxxxxxxxxxx>]

I'm seeing a regression in query optimization after testing a
database+query that was running well on MariaDB 10.0. The issue occurs on
an installation of 10.4.18. Version 10.4 considers the plan that 10.0 used
but dismisses it as having a cost of 2e308 [1]. The plan 10.4 chooses is
highly non-optimal.

Based on reviewing some source code, it looks like this sort of "max" cost
should only result if summing/multiplying other costs would exceed that
maximum.

The usual steps of rebuilding tables and index statistics don't help,
especially because the JSON output for the optimization plan already shows
reasonable index use costs. (The 2e308 cost only starts appearing in a
later section.) Because 10.0 lacks the same optimization tracing as 10.4,
here is the EXPLAIN output for comparison [2].

Hopefully, there's just something simple amiss and posting here helps
anyone else who encounters a 2e308 cost on query optimization plans. I
tried to see if this may be fixed in 10.5+, but I haven't found much
discussion online of this phenomenon.

[1]
https://gist.github.com/davidstrauss/becb03fa75ecac046cafe6f05da9f869#file-optimizer_trace-json
[2]
https://gist.github.com/davidstrauss/becb03fa75ecac046cafe6f05da9f869#file-explain-txt