petascope - create indexes for database relationships in Hibernate

[Bang Pham Huu]

Petascope needs to take care of creating indexes for foreign keys in petascopedb as they are not created by default.

Without indexes, there are some queries run slowly in petascopedb.

For example:

sudo -u postgres psql -d petascopedb -c 'select round(( 100 * total_time / sum(total_time) over ())::numeric, 2) percent,
             round(total_time::numeric, 2) as total,
             calls,
             round(mean_time::numeric, 2) as mean,
             stddev_time,
             query
from pg_stat_statements
order by total_time DESC
limit 10;'

 percent |    total    | calls |  mean  |    stddev_time     |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         query                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
---------+-------------+-------+--------+--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   38.16 | 10673467.04 | 15438 | 691.38 | 141.24315710058394 | update wms13__bounding_box set wms13__layer_id=$2, bounding_boxes_order=$3 where wms13__layer_id=$1
   31.52 |  8817334.79 | 15437 | 571.18 |  119.9697683147309 | update wms13__dimension set wms13__layer_id=$2, dimensions_order=$3 where wms13__layer_id=$1
   15.25 |  4265850.24 | 15439 | 276.30 |  73.31202547298716 | select boundingbo0_.wms13__layer_id as wms7_55_0_, boundingbo0_.wms13__bounding_box_id as wms1_55_0_, boundingbo0_.bounding_boxes_order as bounding8_0_, boundingbo0_.wms13__bounding_box_id as wms1_55_1_, boundingbo0_.wms13__bounding_box_crs as wms2_55_1_, boundingbo0_.wms13__bounding_box_xmax as wms3_55_1_, boundingbo0_.wms13__bounding_box_xmin as wms4_55_1_, boundingbo0_.wms13__bounding_box_ymax as wms5_55_1_, boundingbo0_.wms13__bounding_box_ymin as wms6_55_1_ from wms13__bounding_box boundingbo0_ where boundingbo0_.wms13__layer_id=$1
   13.82 |  3865909.83 | 15439 | 250.40 |  86.63837426468189 | select dimensions0_.wms13__layer_id as wms10_57_0_, dimensions0_.wms13__dimension_id as wms1_57_0_, dimensions0_.dimensions_order as dimensi11_0_, dimensions0_.wms13__dimension_id as wms1_57_1_, dimensions0_.current as current2_57_1_, dimensions0_.default_value as default_3_57_1_, dimensions0_.extent as extent4_57_1_, dimensions0_.multiple_values as multiple5_57_1_, dimensions0_.name as name6_57_1_, dimensions0_.nearest_value as nearest_7_57_1_, dimensions0_.unit_symbol as unit_sym8_57_1_, dimensions0_.units as units9_57_1_ from wms13__dimension dimensions0_ where dimensions0_.wms13__layer_id=$1
    0.71 |   198032.35 | 15439 |  12.83 |  9.032676792799363 | select domainset0_.domain_set_id as domain_s1_15_0_, domainset0_1_.general_grid_id as general_3_21_0_, domainset0_1_.coverage_origin as coverage1_21_0_, case when domainset0_1_.general_grid_domain_set_id is not null then $2 when domainset0_.domain_set_id is not null then $3 end as clazz_0_, generalgri1_.general_grid_id as general_1_19_1_, generalgri1_.grid_limits_id as grid_lim3_19_1_, generalgri1_.srs_name as srs_name2_19_1_, geoaxes2_.general_grid_id as general_6_22_2_, geoaxes2_.geo_axis_id as geo_axis5_22_2_, geoaxes2_.geo_axes_order as geo_axes7_2_, geoaxes2_.geo_axis_id as axis_id1_4_3_, geoaxes2_1_.axis_label as axis_lab2_4_3_, geoaxes2_1_.srs_name as srs_name3_4_3_, geoaxes2_1_.uom_label as uom_labe4_4_3_, geoaxes2_.axis_type as axis_typ1_22_3_, geoaxes2_.lower_bound as lower_bo2_22_3_, geoaxes2_.resolution as resoluti3_22_3_, geoaxes2_.upper_bound as upper_bo4_22_3_, case when geoaxes2_2_.irregular_axis_id is not null then $4 when geoaxes2_3_.regular_axis_id is not null then $5 when geoaxes2_.geo_axis_id is not null then $6 end as clazz_3_, directposi3_.irregular_axis_id as irregula1_28_4_, directposi3_.irregular_axis_direct_positions as irregula2_28_4_, directposi3_.direct_positions_order as direct_p3_4_, gridlimits4_.grid_limits_id as grid_lim1_23_5_, gridlimits4_.srs_name as srs_name2_23_5_, indexaxes5_.grid_limits_id as grid_lim5_24_6_, indexaxes5_.index_axis_id as index_ax4_24_6_, indexaxes5_.index_axes_order as index_ax6_6_, indexaxes5_.index_axis_id as axis_id1_4_7_, indexaxes5_1_.axis_label as axis_lab2_4_7_, indexaxes5_1_.srs_name as srs_name3_4_7_, indexaxes5_1_.uom_label as uom_labe4_4_7_, indexaxes5_.grid_axis_order as grid_axi1_24_7_, indexaxes5_.lower_bound as lower_bo2_24_7_, indexaxes5_.upper_bound as upper_bo3_24_7_ from domain_set domainset0_ left outer join general_grid_domain_set domainset0_1_ on domainset0_.domain_set_id=domainset0_1_.general_grid_domain_set_id left outer join general_grid generalgri1_ on domainset0_1_.general_grid_id=generalgri1_.general_grid_id left outer join geo_axis geoaxes2_ on generalgri1_.general_grid_id=geoaxes2_.general_grid_id left outer join axis geoaxes2_1_ on geoaxes2_.geo_axis_id=geoaxes2_1_.axis_id left outer join irregular_axis geoaxes2_2_ on geoaxes2_.geo_axis_id=geoaxes2_2_.irregular_axis_id left outer join regular_axis geoaxes2_3_ on geoaxes2_.geo_axis_id=geoaxes2_3_.regular_axis_id left outer join irregular_axis_direct_positions directposi3_ on geoaxes2_.geo_axis_id=directposi3_.irregular_axis_id left outer join grid_limits gridlimits4_ on generalgri1_.grid_limits_id=gridlimits4_.grid_limits_id left outer join index_axis indexaxes5_ on gridlimits4_.grid_limits_id=indexaxes5_.grid_limits_id left outer join axis indexaxes5_1_ on indexaxes5_.index_axis_id=indexaxes5_1_.axis_id where domainset0_.domain_set_id=$1
    0.08 |    23074.40 | 15439 |   1.49 | 1.3565830810206057 | select envelope0_.envelope_id as envelope1_16_0_, envelope0_.envelope_by_axis_id as envelope2_16_0_, envelopeby1_.envelope_by_axis_id as envelope1_17_1_, envelopeby1_.axis_labels as axis_lab2_17_1_, envelopeby1_.srs_dimension as srs_dime3_17_1_, envelopeby1_.srs_name as srs_name4_17_1_, envelopeby1_.wgs84_bounding_box_id as wgs5_17_1_, axisextent2_.envelope_by_axis_id as envelope9_5_2_, axisextent2_.axis_extent_id as axis_ext1_5_2_, axisextent2_.axis_extents_order as axis_ex10_2_, axisextent2_.axis_extent_id as axis_ext1_5_3_, axisextent2_.axis_label as axis_lab2_5_3_, axisextent2_.axis_type as axis_typ3_5_3_, axisextent2_.lower_bound as lower_bo4_5_3_, axisextent2_.resolution as resoluti5_5_3_, axisextent2_.srs_name as srs_name6_5_3_, axisextent2_.uom_label as uom_labe7_5_3_, axisextent2_.upper_bound as upper_bo8_5_3_, wgs84bound3_.wgs84_bounding_box_id as wgs1_52_4_, wgs84bound3_.max_lat as max_lat2_52_4_, wgs84bound3_.max_long as max_long3_52_4_, wgs84bound3_.min_lat as min_lat4_52_4_, wgs84bound3_.min_long as min_long5_52_4_ from envelope envelope0_ left outer join envelope_by_axis envelopeby1_ on envelope0_.envelope_by_axis_id=envelopeby1_.envelope_by_axis_id left outer join axis_extent axisextent2_ on envelopeby1_.envelope_by_axis_id=axisextent2_.envelope_by_axis_id left outer join wgs84_bounding_box wgs84bound3_ on envelopeby1_.wgs84_bounding_box_id=wgs84bound3_.wgs84_bounding_box_id where envelope0_.envelope_id=$1
    0.08 |    22887.00 | 15439 |   1.48 | 0.9496411301785505 | select generalgri0_.general_grid_coverage_id as id1_9_12_, generalgri0_1_.coverage_function_id as coverage7_9_12_, generalgri0_1_.coverage_id as coverage2_9_12_, generalgri0_1_.coverage_size_in_bytes as coverage3_9_12_, generalgri0_1_.coverage_type as coverage4_9_12_, generalgri0_1_.domain_set_id as domain_s8_9_12_, generalgri0_1_.envelope_id as envelope9_9_12_, generalgri0_1_.inspire_metadata_url as inspire_5_9_12_, generalgri0_1_.metadata as metadata6_9_12_, generalgri0_1_.range_type_id as range_t10_9_12_, generalgri0_1_.rasdaman_range_set_id as rasdama11_9_12_, coveragefu1_.coverage_function_id as coverage1_10_0_, coveragefu1_.sequence_rule as sequence2_10_0_, domainset2_.domain_set_id as domain_s1_15_1_, domainset2_1_.general_grid_id as general_3_21_1_, domainset2_1_.coverage_origin as coverage1_21_1_, case when domainset2_1_.general_grid_domain_set_id is not null then $2 when domainset2_.domain_set_id is not null then $3 end as clazz_1_, generalgri3_.general_grid_id as general_1_19_2_, generalgri3_.grid_limits_id as grid_lim3_19_2_, generalgri3_.srs_name as srs_name2_19_2_, geoaxes4_.general_grid_id as general_6_22_14_, geoaxes4_.geo_axis_id as geo_axis5_22_14_, geoaxes4_.geo_axes_order as geo_axes7_14_, geoaxes4_.geo_axis_id as axis_id1_4_3_, geoaxes4_1_.axis_label as axis_lab2_4_3_, geoaxes4_1_.srs_name as srs_name3_4_3_, geoaxes4_1_.uom_label as uom_labe4_4_3_, geoaxes4_.axis_type as axis_typ1_22_3_, geoaxes4_.lower_bound as lower_bo2_22_3_, geoaxes4_.resolution as resoluti3_22_3_, geoaxes4_.upper_bound as upper_bo4_22_3_, case when geoaxes4_2_.irregular_axis_id is not null then $4 when geoaxes4_3_.regular_axis_id is not null then $5 when geoaxes4_.geo_axis_id is not null then $6 end as clazz_3_, gridlimits5_.grid_limits_id as grid_lim1_23_4_, gridlimits5_.srs_name as srs_name2_23_4_, envelope6_.envelope_id as envelope1_16_5_, envelope6_.envelope_by_axis_id as envelope2_16_5_, envelopeby7_.envelope_by_axis_id as envelope1_17_6_, envelopeby7_.axis_labels as axis_lab2_17_6_, envelopeby7_.srs_dimension as srs_dime3_17_6_, envelopeby7_.srs_name as srs_name4_17_6_, envelopeby7_.wgs84_bounding_box_id as wgs5_17_6_, wgs84bound8_.wgs84_bounding_box_id as wgs1_52_7_, wgs84bound8_.max_lat as max_lat2_52_7_, wgs84bound8_.max_long as max_long3_52_7_, wgs84bound8_.min_lat as min_lat4_52_7_, wgs84bound8_.min_long as min_long5_52_7_, rangetype9_.range_type_id as range_ty1_38_8_, rangetype9_.data_record_id as data_rec2_38_8_, rangetype9_.interpolation_restriction_id as interpol3_38_8_, datarecord10_.data_record_id as data_rec1_14_9_, interpolat11_.interpolation_restriction_id as interpol1_25_10_, rasdamanra12_.rasdaman_range_set_id as rasdaman1_40_11_, rasdamanra12_.collection_name as collecti2_40_11_, rasdamanra12_.collection_type as collecti3_40_11_, rasdamanra12_.mdd_type as mdd_type4_40_11_, rasdamanra12_.oid as oid5_40_11_, rasdamanra12_.tiling as tiling6_40_11_ from general_grid_coverage generalgri0_ inner join coverage generalgri0_1_ on generalgri0_.general_grid_coverage_id=generalgri0_1_.id left outer join coverage_function coveragefu1_ on generalgri0_1_.coverage_function_id=coveragefu1_.coverage_function_id left outer join domain_set domainset2_ on generalgri0_1_.domain_set_id=domainset2_.domain_set_id left outer join general_grid_domain_set domainset2_1_ on domainset2_.domain_set_id=domainset2_1_.general_grid_domain_set_id left outer join general_grid generalgri3_ on domainset2_1_.general_grid_id=generalgri3_.general_grid_id left outer join geo_axis geoaxes4_ on generalgri3_.general_grid_id=geoaxes4_.general_grid_id left outer join axis geoaxes4_1_ on geoaxes4_.geo_axis_id=geoaxes4_1_.axis_id left outer join irregular_axis geoaxes4_2_ on geoaxes4_.geo_axis_id=geoaxes4_2_.irregular_axis_id left outer join regular_axis geoaxes4_3_ on geoaxes4_.geo_axis_id=geoaxes4_3_.regular_axis_id left outer join grid_limits gridlimits5_ on generalgri3_.grid_limits_id=gridlimits5_.grid_limits_id left outer join envelope envelope6_ on generalgri0_1_.envelope_id=envelope6_.envelope_id left outer join envelope_by_axis envelopeby7_ on envelope6_.envelope_by_axis_id=envelopeby7_.envelope_by_axis_id left outer join wgs84_bounding_box wgs84bound8_ on envelopeby7_.wgs84_bounding_box_id=wgs84bound8_.wgs84_bounding_box_id left outer join range_type rangetype9_ on generalgri0_1_.range_type_id=rangetype9_.range_type_id left outer join data_record datarecord10_ on rangetype9_.data_record_id=datarecord10_.data_record_id left outer join interpolation_restriction interpolat11_ on rangetype9_.interpolation_restriction_id=interpolat11_.interpolation_restriction_id left outer join rasdaman_range_set rasdamanra12_ on generalgri0_1_.rasdaman_range_set_id=rasdamanra12_.rasdaman_range_set_id where generalgri0_.general_grid_coverage_id=$1
    0.06 |    17967.22 | 15439 |   1.16 | 1.0174478387574528 | select axisextent0_.envelope_by_axis_id as envelope9_5_0_, axisextent0_.axis_extent_id as axis_ext1_5_0_, axisextent0_.axis_extents_order as axis_ex10_0_, axisextent0_.axis_extent_id as axis_ext1_5_1_, axisextent0_.axis_label as axis_lab2_5_1_, axisextent0_.axis_type as axis_typ3_5_1_, axisextent0_.lower_bound as lower_bo4_5_1_, axisextent0_.resolution as resoluti5_5_1_, axisextent0_.srs_name as srs_name6_5_1_, axisextent0_.uom_label as uom_labe7_5_1_, axisextent0_.upper_bound as upper_bo8_5_1_ from axis_extent axisextent0_ where axisextent0_.envelope_by_axis_id=$1
    0.06 |    17068.18 | 15439 |   1.11 | 1.2221795839891845 | select rangetype0_.range_type_id as range_ty1_38_0_, rangetype0_.data_record_id as data_rec2_38_0_, rangetype0_.interpolation_restriction_id as interpol3_38_0_, datarecord1_.data_record_id as data_rec1_14_1_, fields2_.data_record_id as data_rec4_18_2_, fields2_.field_id as field_id1_18_2_, fields2_.fields_order as fields_o5_2_, fields2_.field_id as field_id1_18_3_, fields2_.name as name2_18_3_, fields2_.quantity_id as quantity3_18_3_, quantity3_.quantity_id as quantity1_37_4_, quantity3_.data_type as data_typ2_37_4_, quantity3_.definition as definiti3_37_4_, quantity3_.description as descript4_37_4_, quantity3_.uom_id as uom_id5_37_4_, allowedval4_.quantity_id as quantity3_3_5_, allowedval4_.allowed_value_id as allowed_1_3_5_, allowedval4_.allowed_values_order as allowed_4_5_, allowedval4_.allowed_value_id as allowed_1_3_6_, allowedval4_.allowed_values as allowed_2_3_6_, nilvalues5_.quantity_id as quantity4_32_7_, nilvalues5_.nil_value_id as nil_valu1_32_7_, nilvalues5_.nil_values_order as nil_valu5_7_, nilvalues5_.nil_value_id as nil_valu1_32_8_, nilvalues5_.reason as reason2_32_8_, nilvalues5_.value as value3_32_8_, uom6_.uom_id as uom_id1_51_9_, uom6_.code as code2_51_9_, interpolat7_.interpolation_restriction_id as interpol1_25_10_, allowedint8_.interpolation_restriction_id as interpol1_26_11_, allowedint8_.interpolation_restriction_allowed_interpolations as interpol2_26_11_, allowedint8_.allowed_interpolations_order as allowed_3_11_ from range_type rangetype0_ left outer join data_record datarecord1_ on rangetype0_.data_record_id=datarecord1_.data_record_id left outer join field fields2_ on datarecord1_.data_record_id=fields2_.data_record_id left outer join quantity quantity3_ on fields2_.quantity_id=quantity3_.quantity_id left outer join allowed_value allowedval4_ on quantity3_.quantity_id=allowedval4_.quantity_id left outer join nil_value nilvalues5_ on quantity3_.quantity_id=nilvalues5_.quantity_id left outer join uom uom6_ on quantity3_.uom_id=uom6_.uom_id left outer join interpolation_restriction interpolat7_ on rangetype0_.interpolation_restriction_id=interpolat7_.interpolation_restriction_id left outer join interpolationrestriction_allowedinterpolations allowedint8_ on interpolat7_.interpolation_restriction_id=allowedint8_.interpolation_restriction_id where rangetype0_.range_type_id=$1
    0.05 |    14583.16 | 15439 |   0.94 | 0.9279251053496925 | select indexaxes0_.grid_limits_id as grid_lim5_24_0_, indexaxes0_.index_axis_id as index_ax4_24_0_, indexaxes0_.index_axes_order as index_ax6_0_, indexaxes0_.index_axis_id as axis_id1_4_1_, indexaxes0_1_.axis_label as axis_lab2_4_1_, indexaxes0_1_.srs_name as srs_name3_4_1_, indexaxes0_1_.uom_label as uom_labe4_4_1_, indexaxes0_.grid_axis_order as grid_axi1_24_1_, indexaxes0_.lower_bound as lower_bo2_24_1_, indexaxes0_.upper_bound as upper_bo3_24_1_ from index_axis indexaxes0_ inner join axis indexaxes0_1_ on indexaxes0_.index_axis_id=indexaxes0_1_.axis_id where indexaxes0_.grid_limits_id=$1
(10 rows)

Then the top 4 queries take the longest time:

update wms13__bounding_box 
set wms13__layer_id=$2, bounding_boxes_order=$3 
where wms13__layer_id=$1

update wms13__dimension 
set wms13__layer_id=$2, dimensions_order=$3 
where wms13__layer_id=$1

select boundingbo0_.wms13__layer_id as wms7_55_0_, boundingbo0_.wms13__bounding_box_id as wms1_55_0_, boundingbo0_.bounding_boxes_order as bounding8_0_, boundingbo0_.wms13__bounding_box_id as wms1_55_1_, boundingbo0_.wms13__bounding_box_crs as wms2_55_1_, boundingbo0_.wms13__bounding_box_xmax as wms3_55_1_, boundingbo0_.wms13__bounding_box_xmin as wms4_55_1_, boundingbo0_.wms13__bounding_box_ymax as wms5_55_1_, boundingbo0_.wms13__bounding_box_ymin as wms6_55_1_ 
from wms13__bounding_box boundingbo0_ 
where boundingbo0_.wms13__layer_id=$1

select dimensions0_.wms13__layer_id as wms10_57_0_, dimensions0_.wms13__dimension_id as wms1_57_0_, dimensions0_.dimensions_order as dimensi11_0_, dimensions0_.wms13__dimension_id as wms1_57_1_, dimensions0_.current as current2_57_1_, dimensions0_.default_value as default_3_57_1_, dimensions0_.extent as extent4_57_1_, dimensions0_.multiple_values as multiple5_57_1_, dimensions0_.name as name6_57_1_, dimensions0_.nearest_value as nearest_7_57_1_, dimensions0_.unit_symbol as unit_sym8_57_1_, dimensions0_.units as units9_57_1_ 
from wms13__dimension dimensions0_ 
where dimensions0_.wms13__layer_id=$1

This will be done in Spring JPA with Hibernate annotation @Index.