CTE retuns empty instantly, but applying where to the empty set in the main query is slow

[jokershigh]

In the query below, the CTE returns empty instantly. The full query returns, that just filter the empty result, takes 3s, If I remove the where clause, it also returns instantly.

Notice that the query is using the index on procuracoes(data), what seems unexpected, as only the result of the CTE is fultered on this field (see execution plan in the end)

WITH raux1 AS ( SELECT p.codprenotacao, p.data, p.protocolo, p.cprotocolo, p.tipo, p.subtipo, i.codigo AS codpessoal, i.nome, i.cadastro FROM PRENOTACOES p, ctr_pessoal i WHERE p.CODPRENOTACAO = i.CODPRENOTACAO AND i.cadastro STARTING WITH '115464368' AND CHAR_LENGTH(i.cadastro)= 11 ) SELECT * FROM raux1 r WHERE r.tipo = 'A' AND r.SUBTIPO = 'I' AND r."DATA">'2023-10-14' ORDER BY "DATA" DESC, protocolo DESC

Execution plan: nodeName PLAN SORT (JOIN (R P INDEX (IDX1_PRENOTACOES, PRENOTACOES_IDX11, PRENOTACOES_IDX1), R I INDEX (FK_CTR_PESSOAL_1))) SORT JOIN R P INDEX (IDX1_PRENOTACOES( DATA[0,000168] ),PRENOTACOES_IDX11( TIPO[0,100000] ),PRENOTACOES_IDX1( SUBTIPO[0,125000] ))

[jokershigh]

If I exclude "AND r."DATA">'2023-10-14'", the query returns instantly

[mrotteveel]

Is that DATA column really a string-type, or is it a DATE? If so, what happens if you use date '2023-10-14' in the condition instead of just '2023-10-24'?

[hvlad]

Did you fetch all rows or just execute the query ? What is the plan of the query that "returns instantly" ?

[jokershigh]

Data is a date, not a string.

I get the same behaviour with '2023-10-24'.

I just executed the query, did not fetch any rows.

Plan of the query that returns instantly:

PLAN SORT (JOIN (R I INDEX (I_CTR_P_CPF), R P INDEX (PK_PRENOTACOES))) SORT JOIN R I INDEX (I_CTR_P_CPF( CADASTRO[0,000001] ))

Query that returns instantly - the same query without "AND r."DATA">'2023-10-14'":

SELECT p.codprenotacao, p.data, p.protocolo, p.cprotocolo, p.tipo, p.subtipo, i.codigo AS codpessoal, i.nome, i.cadastro FROM PRENOTACOES p, ctr_pessoal i WHERE p.CODPRENOTACAO = i.CODPRENOTACAO AND i.cadastro STARTING WITH '115464368' AND CHAR_LENGTH(i.cadastro)= 11 ) SELECT * FROM raux1 r WHERE r.tipo = 'A' AND r.SUBTIPO = 'I' ORDER BY "DATA" DESC, protocolo DESC

[mrotteveel]

Data is a date, not a string.

I get the same behaviour with '2023-10-24'.

I asked what happened with date '2023-10-24' (a date literal), not just '2023-10-24' (a char literal).

[jokershigh]

Ok. The time it takes is about the same. Adjusted query:

WITH raux1 AS ( SELECT p.codprenotacao, p.data, p.protocolo, p.cprotocolo, p.tipo, p.subtipo, i.codigo AS codpessoal, i.nome, i.cadastro FROM PRENOTACOES p, ctr_pessoal i WHERE p.CODPRENOTACAO = i.CODPRENOTACAO AND i.cadastro STARTING WITH '115464368' AND CHAR_LENGTH(i.cadastro)= 11 ) SELECT * FROM raux1 r WHERE r.tipo = 'A' AND r.SUBTIPO = 'I' AND r."DATA"> date '2023-10-14' ORDER BY "DATA" DESC, protocolo DESC

Execution Plan (Firebird 5.02, DBeaver):

PLAN SORT (JOIN (R P INDEX (IDX1_PRENOTACOES, PRENOTACOES_IDX11, PRENOTACOES_IDX1), R I INDEX (FK_CTR_PESSOAL_1))) SORT JOIN R P INDEX (IDX1_PRENOTACOES( DATA[0,000168] ),PRENOTACOES_IDX11( TIPO[0,100000] ),PRENOTACOES_IDX1( SUBTIPO[0,125000] ))