Hi,
> Looking at the EXPLAIN output it looks
> like the indexes are being used, but it still take 70 seconds to run.
If you see "tableScan" in the query plan, then the index isn't used.
If you see an index, then you also need look at the number of rows
read when using that index.
> FROM PUBLIC."instrument" "s" /* PUBLIC."instrument".tableScan */ /*
> scanCount: 387 */
No index is used here.
> LEFT OUTER JOIN PUBLIC."instrumentidentifier" "s10" /*
> PUBLIC.INSTRMNTIDENTIFIER_INSTRMENT_INDEX_6: "instrument" = "s"."id"
> */
Here an index is used.
> scanCount: 4885
But even when using the index, it had to read that many rows.
> LEFT OUTER JOIN PUBLIC."equityprice" "ep" /*
> PUBLIC.EQUITYPRICE_INSTRUMENT_INDEX_8: "instrument" = "s"."id" */ ON
> "s"."id" = "ep"."instrument" /* scanCount: 13968 */
That's quite a lot of rows.
> LEFT OUTER JOIN PUBLIC."instrumentinstrumenttype" "it" /*
> PUBLIC.INSTRMNTTYP_NSTRMNTTYPNSTRMN_INDEX_2: "instrument" = "s"."id"
> */ ON "it"."instrument" = "s"."id" /* scanCount: 147199 */
10 time more rows.
> LEFT OUTER JOIN PUBLIC."marketidentifier" "mi" /*
> PUBLIC.PRIMARY_KEY_7D: "id" = "imi"."marketidentifier" */ ON
> ("mi"."scheme" = 'ISO 10383') AND ("mi"."id" =
> "imi"."marketidentifier") /* scanCount: 2486621 */
That's probably the biggest problem.
Regards,
Thomas
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
