Hi,
I am using this std. database schema here openmddb.org. I am trying to
write query that performs. Looking at the EXPLAIN output it looks
like the indexes are being used, but it still take 70 seconds to run.
Can someone advise me on what to look for in the EXPLAIN output to
address the slow performance. Here is the output:
SELECT DISTINCT "s"."id", "s10"."code" AS "isin", "d10"."currency" AS
"currency", "loc"."country", "s"."name", "ep"."close" AS "price",
CASEWHEN(("cl"."callable" IS NOT NULL), ("it"."instrumenttype" +
10000),
CASEWHEN(("pt"."putable" IS NOT NULL), ("it"."instrumenttype" +
20000), "it"."instrumenttype")) AS "sectype", "mi"."code" AS MIC, 0 AS
"ai", 0 AS "ai2", "ep"."tradingdate" AS "datestamp", 0 AS "final",
0 AS "valpoint", '0' AS "hrule", '0' AS "hrule2", 0 AS "oid", 0 AS
"zca", 0 AS "frn", 0 AS "rtfeed", CASEWHEN(("cl"."id" IS NULL), 0, 1)
AS "callable", CASEWHEN(("pt"."id" IS NULL), 0, 1) AS "putable",
CASEWHEN(("sk"."id" IS NULL), 0, 1) AS "sinkable",
CASEWHEN(("nfr"."id" IS NULL), 0, 1) AS "floater", 1 AS "finalised", 0
AS "strikeYield", 0 AS "IssueYield", 0 AS "NetByCpty", "s"."name" AS
"description",
0 AS "priceOnUnderlying", 0 AS "inActive", "e"."name" AS "issuername"/
n
FROM PUBLIC."instrument" "s" /* PUBLIC."instrument".tableScan */ /*
scanCount: 387 *//n
LEFT OUTER JOIN PUBLIC."instrumentidentifier" "s10" /*
PUBLIC.INSTRMNTIDENTIFIER_INSTRMENT_INDEX_6: "instrument" = "s"."id"
*/ ON ("s10"."schemeinfo" = 3) AND ("s"."id" = "s10"."instrument") /*
scanCount: 4885 *//n
LEFT OUTER JOIN PUBLIC."denomination" "d10" /*
PUBLIC.DENOMINATION_INSTRUMENT_INDEX_A: "instrument" = "s"."id" */ ON
"s"."id" = "d10"."instrument" /* scanCount: 1729 *//n
LEFT OUTER JOIN PUBLIC."issuer" "i10" /* PUBLIC.PRIMARY_KEY_14E34:
"entity_id" = "s"."issuer" */ ON "i10"."entity_id" = "s"."issuer" /*
scanCount: 3209 *//n
LEFT OUTER JOIN PUBLIC."location" "loc" /*
PUBLIC.ENTITY_LOCATION_INDEX_1: "entity" = "i10"."entity_id" */ ON
"loc"."entity" = "i10"."entity_id" /* scanCount: 3568 *//n
LEFT OUTER JOIN PUBLIC."equityprice" "ep" /*
PUBLIC.EQUITYPRICE_INSTRUMENT_INDEX_8: "instrument" = "s"."id" */ ON
"s"."id" = "ep"."instrument" /* scanCount: 13968 *//n
LEFT OUTER JOIN PUBLIC."instrumentinstrumenttype" "it" /*
PUBLIC.INSTRMNTTYP_NSTRMNTTYPNSTRMN_INDEX_2: "instrument" = "s"."id"
*/ ON "it"."instrument" = "s"."id" /* scanCount: 147199 *//n
LEFT OUTER JOIN PUBLIC."callable" "cl" /*
PUBLIC.CALLABLE_INSTRUMENT_INDEX_F: "instrument" = "s"."id" */ ON
"cl"."instrument" = "s"."id" /* scanCount: 134327 *//n
LEFT OUTER JOIN PUBLIC."putable" "pt" /*
PUBLIC.PUTABLE_INSTRUMENT_INDEX_F: "instrument" = "s"."id" */ ON
"pt"."instrument" = "s"."id" /* scanCount: 134327 *//n
LEFT OUTER JOIN PUBLIC."sinkable" "sk" /* PUBLIC."sinkable".tableScan
*/ ON "sk"."sinkable" = "s"."id" /* scanCount: 134327 *//n
LEFT OUTER JOIN PUBLIC."interestrate" "ir" /*
PUBLIC.INTERESTRATE_INSTRUMENT_INDEX_3: "instrument" = "s"."id" */ ON
"ir"."instrument" = "s"."id" /* scanCount: 134327 *//n
LEFT OUTER JOIN PUBLIC."nonfixedrate" "nfr" /* PUBLIC.PRIMARY_KEY_1A8:
"id" = "ir"."id" */ ON "nfr"."id" = "ir"."id" /* scanCount: 134327 *//
n
LEFT OUTER JOIN PUBLIC."instrumentmarketidentifier" "imi" /*
PUBLIC.MRKTDNTFR_MRKTIDNTFRINSTRMNT_INDEX_5: "instrument" = "s"."id"
*/ ON "imi"."instrument" = "s"."id" /* scanCount: 1377375 *//n
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 *//n
LEFT OUTER JOIN PUBLIC."entity" "e" /* PUBLIC.PRIMARY_KEY_B2: "id" =
"s"."issuer" */ ON "e"."id" = "s"."issuer" /* scanCount: 2487143 *//n
WHERE ("s10"."code" IS NOT NULL) AND ("ep"."tradingdate" = (SELECT
MAX("p1"."tradingdate") AS "maxdate"/n
FROM PUBLIC."equityprice" "p1" /*
PUBLIC.EQUITYPRICE_INSTRUMENT_INDEX_8: "instrument" =
"ep"."instrument" */ /* scanCount: 12 *//n
WHERE "ep"."instrument" = "p1"."instrument"))/n
/*/ntotal: 566/ndenomination.DENOMINATION_INSTRUMENT_INDEX_A read: 7
(1%)/n
denomination.denomination_DATA read: 39 (6%)/n
entity.entity_DATA read: 84 (14%)/
nequityprice.EQUITYPRICE_INSTRUMENT_INDEX_8 read: 7 (1%)/n
equityprice.equityprice_DATA read: 96 (16%)/n
instrument.instrument_DATA read: 24 (4%)/n
instrumentidentifier.INSTRMNTIDENTIFIER_INSTRMENT_INDEX_6 read: 23
(4%)/n
instrumentidentifier.instrumentidentifier_DATA read: 199 (35%)/n
instrumentinstrumenttype.INSTRMNTTYP_NSTRMNTTYPNSTRMN_INDEX_2 read: 9
(1%)/n
instrumentinstrumenttype.instrumentinstrumenttype_DATA read: 18 (3%)/n
instrumentmarketidentifier.MRKTDNTFR_MRKTIDNTFRINSTRMNT_INDEX_5 read:
7 (1%)/n
instrumentmarketidentifier.instrumentmarketidentifier_DATA read: 18
(3%)/n
issuer.PRIMARY_KEY_14E34 read: 10 (1%)/n
location.ENTITY_LOCATION_INDEX_1 read: 8 (1%)/n
location.location_DATA read: 7 (1%)/n
marketidentifier.marketidentifier_DATA read: 10 (1%)/n*/
Thx.
David
--
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.
