This query:
WITH TMP( TJENESTEENHETRESHID, TJENESTEENHETLOKAL, IMPORT_DATO ) AS
( SELECT TJENESTEENHETRESHID, TJENESTEENHETLOKAL, MAX( IMPORT_DATO )
FROM NPR
WHERE TJENESTEENHETRESHID > 0
AND TJENESTEENHETLOKAL > ''
GROUP BY 1, 2 ),
TMP2( TJENESTEENHETRESHID, TJENESTEENHETLOKAL ) AS
( SELECT TJENESTEENHETRESHID, TJENESTEENHETLOKAL
FROM TMP T
WHERE NOT EXISTS( SELECT * FROM TMP T2
WHERE T.TJENESTEENHETRESHID = T2.TJENESTEENHETRESHID
AND T.IMPORT_DATO < T2.IMPORT_DATO ) )
SELECT TJENESTEENHETRESHID, LIST( TJENESTEENHETLOKAL ) TJENESTEENHETLOKAL
FROM TMP2
GROUP BY 1
HAVING COUNT( DISTINCT TJENESTEENHETLOKAL ) = 1
generates this plan:
PLAN SORT ((TMP2 T2 NPR NATURAL))
PLAN SORT ((TMP2 T NPR NATURAL))
NPR is a medium sized table (I'm guessing 20 million rows), but the result
of the first CTE is only 2200 rows. None of the fields in this particular
query are indexed. I tried to run the query, but decided to terminate it
after about 2 hours 40 minutes.
If I run the first CTE separately
( SELECT TJENESTEENHETRESHID, TJENESTEENHETLOKAL, MAX( IMPORT_DATO )
FROM NPR
WHERE TJENESTEENHETRESHID > 0
AND TJENESTEENHETLOKAL > ''
GROUP BY 1, 2 )
it takes just over 16 minutes. Putting the result of this CTE in a
temporary table and running the rest of the query against this temporary
table only takes a couple of seconds (reducing the 2200 rows to 1600). The
way I normally read plans seems to fail me this time - I don't quite
understand why NPR is mentioned twice in the plan (I assume it has to do
with my "two-level" GROUP BYs). Can anyone explain why? Or better: Does
anyone understand why the query takes so long to execute?
This is on Firebird 2.5 [2.5.4.26856]
Thanks in advance for any hints,
Set
