Joining a table with itself is an issue?!

[areichel]

Hi guys, simple use case:

CREATE TABLE RISKBOX.RISK.EXT_RATING_MIGRATION_DATA ( 
    VALUE_DATE        DATE NOT NULL
    , ID_INSTRUMENT        VARCHAR(40) NOT NULL
    , ID_INSTRUMENT_TYPE        VARCHAR(12) NOT NULL
    , PRODUCT        VARCHAR(40) NULL
    , ID_COUNTER_PARTY        VARCHAR(40) NULL
    , ID_PORTFOLIO        VARCHAR(40) NULL
    , MASTER_RATING        VARCHAR(2) NULL
    , RISK_CLASSIFICATION        VARCHAR(10) NULL
    , OVERDUE_DAYS        DECIMAL(4) NULL
    , DEFAULT_FLAG        VARCHAR(1) NULL
    , ID_CURRENCY        VARCHAR(3) NOT NULL
    , BOOK_VALUE        DECIMAL(23,5) NOT NULL
    , UNDRAWN_AMOUNT        DECIMAL(23,5) NOT NULL
);
CREATE INDEX EXT_RATING_MIGRATION_DATA_IDX1
    ON RISKBOX.RISK.EXT_RATING_MIGRATION_DATA(VALUE_DATE, MASTER_RATING);
CREATE UNIQUE INDEX EXT_RATING_MIGRATION_DATA_IDX2
    ON RISKBOX.RISK.EXT_RATING_MIGRATION_DATA(VALUE_DATE, ID_INSTRUMENT);
CREATE INDEX EXT_RATING_MIGRATION_DATA_IDX3
    ON RISKBOX.RISK.EXT_RATING_MIGRATION_DATA(VALUE_DATE);

Table has 1 Mill. rows, I have 16 cores with 32 GB RAM and SSD. 8GB case assigned to the H2 database.

However, the following query NEVER returns and also getting the EXPLAIN does never return:

EXPLAIN ANALYZE SELECT ex.VALUE_DATE
                     , a.ID_INSTRUMENT
                     , a.OVERDUE_DAYS
                     , a.DEFAULT_FLAG
                     , a.BOOK_VALUE
FROM   risk.ext_rating_migration_data ex
       INNER JOIN risk.ext_rating_migration_data a
               ON a.value_date = ex.value_date;

--> no results even after an hour, I can't even get the query plan. Neither any results.

I have tried to fully analyze the DB already, compacted it. Tried many times, various statements.

As long as I join two different tables, performance is good. As soon as I join a table with itself, I do not get results.

H2 Version is recent 1.4.201-pre.

Any idea what the problem could be and how to solve it?

Best regards

Andreas

[Noel Grandin]

try doing

   EXPLAIN

instead of

  EXPLAIN ANALYZE

the second ones actually runs the query and records times, the first one just returns the query plan

[areichel]

Oh! Thank you, I was not aware of that.

Best regards

Andreas

[Andrei Tokar]

I wonder how many rows do you expect to come back.

Even if you have 30 year of history in your table (an given a financial nature of your data

it can't be significantly longer than that), it would give us ~ 10,000 dates, which would yield

100 rows per date on average.

Under such assumption join result might have 100,000,000 rows.

At least it has to be executed in a lazy fashion, but most likely you need some limiting conditions.