Hello,
I have query with multiple inner joins.
-openstreetmap data separated into several tables
The execution time seems to be quite long(~30 sec). I suppose it could
be reduced but I have no idea how to achieve this.
QUERY:
select distinct * from (
select ways_nodes.way_id, tag_keys.key, tags.value from (
select nodes.node_id from nodes
where latitude>=49.00 and latitude<=49.22 and
longitude>=14.88 and longitude<=15.10
) as selected_nodes
inner join ways_nodes on
selected_nodes.node_id=ways_nodes.node_id
inner join ways_tags on
ways_nodes.way_id=ways_tags.way_id
inner join tags on ways_tags.tag_id=tags.tag_id
inner join tag_keys on tags.key_id=tag_keys.key_id
)
EXPLAIN ANALYZE result:
SELECT DISTINCT
_48.WAY_ID,
_48.KEY,
_48.VALUE
FROM (
SELECT
WAYS_NODES.WAY_ID,
TAG_KEYS.KEY,
TAGS.VALUE
FROM (
SELECT
NODES.NODE_ID
FROM PUBLIC.NODES
/* PUBLIC.INDEX__NODES__LON_LAT: LONGITUDE <= 15.10
AND LONGITUDE >= 14.88
AND LATITUDE >= 49.00
AND LATITUDE <= 49.22
*/
WHERE (LONGITUDE <= 15.10)
AND ((LONGITUDE >= 14.88)
AND ((LATITUDE >= 49.00)
AND (LATITUDE <= 49.22)))
) X
/* SELECT
NODES.NODE_ID
FROM PUBLIC.NODES
/++ PUBLIC.INDEX__NODES__LON_LAT: LONGITUDE <= 15.10
AND LONGITUDE >= 14.88
AND LATITUDE >= 49.00
AND LATITUDE <= 49.22
++/
WHERE (LONGITUDE <= 15.10)
AND ((LONGITUDE >= 14.88)
AND ((LATITUDE >= 49.00)
AND (LATITUDE <= 49.22)))
*/
INNER JOIN PUBLIC.WAYS_NODES
/* PUBLIC.INDEX__WAYS_NODES__NODE_ID: NODE_ID = X.NODE_ID */
ON 1=1
/* WHERE X.NODE_ID = WAYS_NODES.NODE_ID
*/
INNER JOIN PUBLIC.WAYS_TAGS
/* PUBLIC.INDEX__WAYS_TAGS__WAY_ID: WAY_ID = WAYS_NODES.WAY_ID
*/
ON 1=1
/* WHERE WAYS_NODES.WAY_ID = WAYS_TAGS.WAY_ID
*/
INNER JOIN PUBLIC.TAGS
/* PUBLIC.PRIMARY_KEY_27: TAG_ID = WAYS_TAGS.TAG_ID */
ON 1=1
/* WHERE WAYS_TAGS.TAG_ID = TAGS.TAG_ID
*/
INNER JOIN PUBLIC.TAG_KEYS
/* PUBLIC.PRIMARY_KEY_2: KEY_ID = TAGS.KEY_ID */
ON 1=1
WHERE (TAGS.KEY_ID = TAG_KEYS.KEY_ID)
AND ((WAYS_TAGS.TAG_ID = TAGS.TAG_ID)
AND ((WAYS_NODES.WAY_ID = WAYS_TAGS.WAY_ID)
AND (X.NODE_ID = WAYS_NODES.NODE_ID)))
) _48
/* SELECT
WAYS_NODES.WAY_ID,
TAG_KEYS.KEY,
TAGS.VALUE
FROM (
SELECT
NODES.NODE_ID
FROM PUBLIC.NODES
/++ PUBLIC.INDEX__NODES__LON_LAT: LONGITUDE <= 15.10
AND LONGITUDE >= 14.88
AND LATITUDE >= 49.00
AND LATITUDE <= 49.22
++/
WHERE (LONGITUDE <= 15.10)
AND ((LONGITUDE >= 14.88)
AND ((LATITUDE >= 49.00)
AND (LATITUDE <= 49.22)))
) X
/++ SELECT
NODES.NODE_ID
FROM PUBLIC.NODES
/++ PUBLIC.INDEX__NODES__LON_LAT: LONGITUDE <= 15.10
AND LONGITUDE >= 14.88
AND LATITUDE >= 49.00
AND LATITUDE <= 49.22
++/
WHERE (LONGITUDE <= 15.10)
AND ((LONGITUDE >= 14.88)
AND ((LATITUDE >= 49.00)
AND (LATITUDE <= 49.22)))
++/
INNER JOIN PUBLIC.WAYS_NODES
/++ PUBLIC.INDEX__WAYS_NODES__NODE_ID: NODE_ID = X.NODE_ID ++/
ON 1=1
/++ WHERE X.NODE_ID = WAYS_NODES.NODE_ID
++/
INNER JOIN PUBLIC.WAYS_TAGS
/++ PUBLIC.INDEX__WAYS_TAGS__WAY_ID: WAY_ID =
WAYS_NODES.WAY_ID ++/
ON 1=1
/++ WHERE WAYS_NODES.WAY_ID = WAYS_TAGS.WAY_ID
++/
INNER JOIN PUBLIC.TAGS
/++ PUBLIC.PRIMARY_KEY_27: TAG_ID = WAYS_TAGS.TAG_ID ++/
ON 1=1
/++ WHERE WAYS_TAGS.TAG_ID = TAGS.TAG_ID
++/
INNER JOIN PUBLIC.TAG_KEYS
/++ PUBLIC.PRIMARY_KEY_2: KEY_ID = TAGS.KEY_ID ++/
ON 1=1
WHERE (TAGS.KEY_ID = TAG_KEYS.KEY_ID)
AND ((WAYS_TAGS.TAG_ID = TAGS.TAG_ID)
AND ((WAYS_NODES.WAY_ID = WAYS_TAGS.WAY_ID)
AND (X.NODE_ID = WAYS_NODES.NODE_ID)))
*/
/* scanCount: 22018 */
Description of query idea:
1. select nodes from given area (table nodes: ~9 000 000 rows but only
~20 000 selected)
2. get ways(ways are composed of nodes) which contains at least one of
previously selected nodes (table nodes_ways: ~9 000 000 rows)
-> inner join to previous select
So I have ways in given area.
3. Then I need to get other data related to selected ways(next several
joins) tables with from 20 to 300 000 rows
4. ensure to get each way only once (DISTINCT)
________
Query selects ~20000 rows. Each other inner join selects no more than
this number of rows from its own table.
Indexes are used(see EXPLAIN ANALYZE).
Is there any obvious mistake in the query?
Or is it common behaviour of similar queries?
I would be glad for any suggestions.
Martin
--
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.
