Worst plan sort created to execute an indexed tables

[n3rv0u5]

In attachment I created a sample database to execute the follow query:

SELECT R.ID_OP, R.ID_REC, SUM(M.Q_MAT * CUS.CUS_MED)
FROM PCP_TIN_REC R
JOIN PCP_TIN_REC_MAT M ON (R.ID_REC = M.ID_REC)
JOIN INV_ETQ_MAT CUS ON (CUS.ID_MAT = M.ID_MAT) AND (CUS.ANOMES = R.AM_BAI)
JOIN INV_ETQ_NAT NAT ON (NAT.ID_NAT = CUS.ID_NAT)
WHERE (NAT.CML_STAT = 1) AND (R.ID_OP = 216262)
GROUP BY R.ID_OP, R.ID_REC

Executing this query without specified a plan sort, Firebird 4.0 use no indexed read.

On the other hand, if a I used a specific plan sort, like:

SELECT R.ID_OP, R.ID_REC, SUM(M.Q_MAT * CUS.CUS_MED)
FROM PCP_TIN_REC R
JOIN PCP_TIN_REC_MAT M ON (R.ID_REC = M.ID_REC)
JOIN INV_ETQ_MAT CUS ON (CUS.ID_MAT = M.ID_MAT) AND (CUS.ANOMES = R.AM_BAI)
JOIN INV_ETQ_NAT NAT ON (NAT.ID_NAT = CUS.ID_NAT)
WHERE (NAT.CML_STAT = 1) AND (R.ID_OP = 216262)
GROUP BY R.ID_OP, R.ID_REC
PLAN SORT (JOIN (R INDEX (FK_PCP_TIN_REC_OP), M INDEX (FK_PCP_TIN_REC_MAT_REC), CUS INDEX (IDX_INV_ETQ_MAT_ANOMES), NAT INDEX (PK_INV_ETQ_NAT)))
TEST.zip

Using this form, the query is very faster.

By the way, on FB 2.5 this is the plan sort generated automatically

Settings: FB 4.0 | ServerMode: SuperClassic | Windows 10 Pro 64bits

Sincerelly,
Rodrigo.

[dyemanov]

What exactly FB v4.0 version (up to build number) do you use? Because with 4.0.3.2840 I see:

PLAN SORT (JOIN (R INDEX (FK_PCP_TIN_REC_OP), M INDEX (FK_PCP_TIN_REC_MAT_REC), CUS INDEX (IDX_INV_ETQ_MAT_ANOMES), NAT INDEX (PK_INV_ETQ_NAT)))

which is exactly your "fast" option.

[n3rv0u5]

I'm using Firebird-4.0.2.2816 <https://github.com/FirebirdSQL/firebird/releases/download/v4.0.2/Firebird-4.0.2.2816-0.tar.xz>. It is the only version available on https://firebirdsql.org Thanks, Rodrigo.

…

On Fri, Nov 25, 2022 at 2:49 PM Dmitry Yemanov ***@***.***> wrote: What exactly FB v4.0 version (up to build number) do you use? Because with 4.0.3.2840 I see: PLAN SORT (JOIN (R INDEX (FK_PCP_TIN_REC_OP), M INDEX (FK_PCP_TIN_REC_MAT_REC), CUS INDEX (IDX_INV_ETQ_MAT_ANOMES), NAT INDEX (PK_INV_ETQ_NAT))) which is exactly your "fast" option. — Reply to this email directly, view it on GitHub <#7398 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AP63SESLESOI7TOWFSA6UNDWKD3Z3ANCNFSM6AAAAAASLIUF2M> . You are receiving this because you authored the thread.Message ID: ***@***.***>

-- Rodrigo Coelho

[dyemanov]

Confirmed the worse plan for v4.0.2, it's already fixed in the upcoming v4.0.3.