Subject Where's my logic flawed? Svein Erling Tysvær 2016-06-03T08:27:05Z
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