On Sun, Aug 11, 2019 at 03:59:06PM -0400, Tom Lane wrote:
> I hacked temp.sql to print a couple different plans (doing it that way,
> rather than manually, just to ensure that I was getting plans matching
> what would actually happen right there). And what I see, as attached,
> is that IOS and plain index and bitmap scans all have pretty much the
> same total cost. The planner then ought to prefer IOS or plain on the
> secondary grounds of cheaper startup cost. However, it's not so hard
> to believe that it might switch to bitmap if something caused the cost
> estimates to change by a few percent. So probably we should write this
> off as "something affected the plan choice" and just add the ORDER BY
> as you suggest.
That matches what I was seeing, except that I have done those tests
manually. Still my plans matched with yours.
> Hmm, I wasn't thinking of changing anything more than this one query.
> I'm not sure that a wide-ranging patch is going to be worth the
> potential back-patching land mines it'd introduce. However, if you
> want to do it anyway, please at least patch v12 as well --- that
> should still be a pretty painless back-patch, even if it's not so
> easy to go further.
Okay, I have gone with a minimal fix of only changing some of the
quals in temp.sql as it could become a problem if other tests begin to
use relations beginning with "temp". If it proves that we have other
problems in this area later on, let's address it at this time.
> BTW, most of the problem here seems to be that the SQL committee
> made an infelicitous choice of wildcard characters for LIKE.
> I wonder if it'd be saner to fix this by switching to regexes?
So that enforces the start of the string to match. This has the merit
to make the relation name cleaner to grab. I have gone with your
suggestion, thanks for the advice!
--
Michael