Index Gist non utlisé

F.Chanson · 27/06/2011 12:40:46

Bonjour,

cette requete sql retourne 0 en 10 secondes sur une base 844 en postgis 1.5 , cela est du au fait que la requête sur les dates retournent une grosse partie sur la base (750.000 rows sur 800.000), par contre le critere geo retourne 0 rows .

ma question :
pourquoi l'optimiseur n'utilise par l'index gist ? et comment forcer l'optimiseur a utilisé l'index geo en premier ... afin de répondre en moins de 1 seconde

requete sql
-------------
select metadatas1_.EXTERNAL_ID as col_0_0_
from DG_PRODUCT dgproductf0_
inner join MD_METADATA metadatas1_ on dgproductf0_.id=metadatas1_.product
left outer join CATALOG_ID catalogide2_ on metadatas1_.fk_catalogID=catalogide2_.ID
left outer join IMAGE_EXTENSION imageexten3_ on metadatas1_.id=imageexten3_.metadata
where
catalogide2_.profile='LEVEL0'
and metadatas1_.attribute_description='SCENE'
and dgproductf0_.archive_searchByAllUsers='t'
and (imageexten3_.begin_viewing_date_time between '2005-07-21 02:32:38.656' and '2008-07-17 16:09:20.128')
and imageexten3_.cloud_cover_percentage>='3.125'
and st_intersects (metadatas1_.geometry, '0106000020E610000003000000010300000001000000060000000000008002E872C000000080942A13C0000000C0007C72C0000000E018020040000000E0CDD472C000000060E8D813C000000020FB5973C0000000804A852BC00000008002E872C000000080942A13C00000008002E872C000000080942A13C00103000000010000000600000000000060ECBF4C4000000080942A13C000000020FD0F5040000000E018020040000000A090594D4000000060E8D813C00000006027304940000000804A852BC000000060ECBF4C4000000080942A13C000000060ECBF4C4000000080942A13C00103000000010000000600000000000080FD177A4000000080942A13C000000040FF837A40000000E01802004000000020322B7A4000000060E8D813C0000000E004A67940000000804A852BC000000080FD177A4000000080942A13C000000080FD177A4000000080942A13C0'
and (metadatas1_.EXTERNAL_ID like '%')
limit '600'

Explain analyse
-------------------
"Limit (cost=0.00..38984.41 rows=600 width=22) (actual time=10180.527..10180.527 rows=0 loops=1)"
" Output: metadatas1_.external_id"
" -> Nested Loop (cost=0.00..880657.90 rows=13554 width=22) (actual time=10180.526..10180.526 rows=0 loops=1)"
" Output: metadatas1_.external_id"
" Join Filter: ((metadatas1_.fk_catalogid)::text = (catalogide2_.id)::text)"
" -> Seq Scan on catalog_id catalogide2_ (cost=0.00..1.01 rows=1 width=37) (actual time=0.005..0.005 rows=1 loops=1)"
" Output: catalogide2_.id, catalogide2_.familytype, catalogide2_.producttype, catalogide2_.profile, catalogide2_.template"
" Filter: ((profile)::text = 'LEVEL0'::text)"
" -> Nested Loop (cost=0.00..880487.46 rows=13554 width=58) (actual time=10180.517..10180.517 rows=0 loops=1)"
" Output: metadatas1_.external_id, metadatas1_.fk_catalogid"
" -> Nested Loop (cost=0.00..871441.01 rows=13554 width=94) (actual time=10180.517..10180.517 rows=0 loops=1)"
" Output: metadatas1_.external_id, metadatas1_.product, metadatas1_.fk_catalogid"
" -> Seq Scan on image_extension imageexten3_ (cost=0.00..48766.17 rows=754357 width=37) (actual time=0.004..534.509 rows=754271 loops=1)"
" Output: imageexten3_.id, imageexten3_.bh_ratio, imageexten3_.accross_track_incidence_angle, imageexten3_.along_track_incidence_angle, imageexten3_.antimeridian_longitude_convention, imageexten3_.begin_viewing_date_time, imageexten3_.campagne, imageexten3_.crs, imageexten3_.geometry, imageexten3_.center_viewing_date_time, imageexten3_.cloud_cover_confidence, imageexten3_.cloud_cover_notation_mode, imageexten3_.cloud_cover_percentage, imageexten3_.colour, imageexten3_.combined_mode, imageexten3_.x_name_type_code, imageexten3_.y_name_type_code, imageexten3_.elevation_angle, imageexten3_.end_viewing_date_time, imageexten3_.gain_number, imageexten3_.helios_gains_list, imageexten3_.helios_line_shift, imageexten3_.helios_loc_quality, imageexten3_.helios_mode_cu, imageexten3_.helios_retina, imageexten3_.helios_viewing_mode, imageexten3_.illumination_azimuth_angle, imageexten3_.illumination_elevation_angle, imageexten3_.incidence_angle, imageexten3_.orbit, imageexten3_.orbit_cycle, imageexten3_.orbit_direction, imageexten3_.orientation_angle, imageexten3_.pass_id, imageexten3_.pitch, imageexten3_.processing_level_code, imageexten3_.quality_code, imageexten3_.quality_notation_confidence, imageexten3_.quality_notation_mode, imageexten3_.quality_percentage, imageexten3_.received_aos, imageexten3_.received_los, imageexten3_.resolution_x, imageexten3_.resolution_y, imageexten3_.revolution, imageexten3_.roll, imageexten3_.scene_rank, imageexten3_.segment_max_global_angle, imageexten3_.segment_min_global_angle, imageexten3_.segment_rank, imageexten3_.sensor_operational_mode, imageexten3_.snow_cover_percentage, imageexten3_.swath, imageexten3_.swath_type, imageexten3_.viewing_id, imageexten3_.yaw, imageexten3_.metadata"
" Filter: ((begin_viewing_date_time >= '2005-07-21 02:32:38.656'::timestamp without time zone) AND (begin_viewing_date_time <= '2008-07-17 16:09:20.128'::timestamp without time zone) AND (cloud_cover_percentage >= 3.125::real))"
" -> Index Scan using ib_t_md_metadata_f_id on md_metadata metadatas1_ (cost=0.00..1.08 rows=1 width=130) (actual time=0.012..0.012 rows=0 loops=754271)"
" Output: metadatas1_.id, metadatas1_.accesses, metadatas1_.complete, metadatas1_.external_id, metadatas1_.insertion_date, metadatas1_.last_update_date, metadatas1_.abstract_id, metadatas1_.access_count, metadatas1_.coord_path, metadatas1_.coord_row, metadatas1_.association_type, metadatas1_.attribute_description, metadatas1_.authority, metadatas1_.citationidentifier, metadatas1_.classification, metadatas1_.archive_format, metadatas1_.data_quality_lineage_process_step_source, metadatas1_.data_quality_process_step_source_description, metadatas1_.datestamp, metadatas1_.dimension_size, metadatas1_.edition, metadatas1_.file_identifier, metadatas1_.format, metadatas1_.format_edition, metadatas1_.crs, metadatas1_.geometry, metadatas1_.geographic_element_identifier, metadatas1_.gipp_parameter_id, metadatas1_.gipp_type, metadatas1_.identification_info_priority, metadatas1_.identification_info_status, metadatas1_.last_access_date, metadatas1_.local_language, metadatas1_.passive_mode, metadatas1_.position_in_product, metadatas1_.process_step_date_time_stamp, metadatas1_.processing_date_time, metadatas1_.resolution_equivalent_scale, metadatas1_.source_metadata_id, metadatas1_.spatial_representation_class, metadatas1_.title, metadatas1_.transfert_option_original_size, metadatas1_.usage_date_time, metadatas1_.fk_metadata_resp, metadatas1_.fk_source_resp, metadatas1_.fk_import_resp, metadatas1_.fk_acquisition, metadatas1_.fk_catalogid, metadatas1_.fk_srid, metadatas1_.product"
" Index Cond: ((metadatas1_.id)::text = (imageexten3_.metadata)::text)"
" Filter: ((metadatas1_.geometry && '0106000020E610000003000000010300000001000000060000000000008002E872C000000080942A13C0000000C0007C72C0000000E018020040000000E0CDD472C000000060E8D813C000000020FB5973C0000000804A852BC00000008002E872C000000080942A13C00000008002E872C000000080942A13C00103000000010000000600000000000060ECBF4C4000000080942A13C000000020FD0F5040000000E018020040000000A090594D4000000060E8D813C00000006027304940000000804A852BC000000060ECBF4C4000000080942A13C000000060ECBF4C4000000080942A13C00103000000010000000600000000000080FD177A4000000080942A13C000000040FF837A40000000E01802004000000020322B7A4000000060E8D813C0000000E004A67940000000804A852BC000000080FD177A4000000080942A13C000000080FD177A4000000080942A13C0'::geometry) AND ((metadatas1_.external_id)::text ~~ '%'::text) AND ((metadatas1_.attribute_description)::text = 'SCENE'::text) AND _st_intersects(metadatas1_.geometry, '0106000020E610000003000000010300000001000000060000000000008002E872C000000080942A13C0000000C0007C72C0000000E018020040000000E0CDD472C000000060E8D813C000000020FB5973C0000000804A852BC00000008002E872C000000080942A13C00000008002E872C000000080942A13C00103000000010000000600000000000060ECBF4C4000000080942A13C000000020FD0F5040000000E018020040000000A090594D4000000060E8D813C00000006027304940000000804A852BC000000060ECBF4C4000000080942A13C000000060ECBF4C4000000080942A13C00103000000010000000600000000000080FD177A4000000080942A13C000000040FF837A40000000E01802004000000020322B7A4000000060E8D813C0000000E004A67940000000804A852BC000000080FD177A4000000080942A13C000000080FD177A4000000080942A13C0'::geometry))"
" -> Index Scan using dg_product_pkey on dg_product dgproductf0_ (cost=0.00..0.65 rows=1 width=37) (never executed)"
" Output: dgproductf0_.id, dgproductf0_.accesses, dgproductf0_.complete, dgproductf0_.external_id, dgproductf0_.insertion_date, dgproductf0_.last_update_date, dgproductf0_.archive_id, dgproductf0_.archiving_center, dgproductf0_.archiving_date, dgproductf0_.archive_expiry, dgproductf0_.archive_key, dgproductf0_.last_updade_date, dgproductf0_.archive_nearline, dgproductf0_.archive_offline, dgproductf0_.archive_online, dgproductf0_.publication_date, dgproductf0_.archive_searchbyallusers, dgproductf0_.archive_size, dgproductf0_.lock_owner, dgproductf0_.locked"
" Index Cond: ((dgproductf0_.id)::text = (metadatas1_.product)::text)"
" Filter: dgproductf0_.archive_searchbyallusers"
"Total runtime: 10285.736 ms"

"

cedric · 03/07/2011 23:54:58

Je suppose qu'utiliser une requete de ce genre peut aider en ce sens, mais il peut exister des raisons pertinentes pour lesquelles le planner de postgresql choisi ce plan:

WITH m1 as (
select EXTERNAL_ID, product, fk_catalogID, id
from MD_METADATA
where attribute_description='SCENE'
and st_intersects (geometry, '0106000020E610000003000000010300000001000000060000000000008002E872C000000080942A13C0000000C0007C72C0000000E018020040000000E0CDD472C000000060E8D813C000000020FB5973C0000000804A852BC00000008002E872C000000080942A13C00000008002E872C000000080942A13C00103000000010000000600000000000060ECBF4C4000000080942A13C000000020FD0F5040000000E018020040000000A090594D4000000060E8D813C00000006027304940000000804A852BC000000060ECBF4C4000000080942A13C000000060ECBF4C4000000080942A13C00103000000010000000600000000000080FD177A4000000080942A13C000000040FF837A40000000E01802004000000020322B7A4000000060E8D813C0000000E004A67940000000804A852BC000000080FD177A4000000080942A13C000000080FD177A4000000080942A13C0'
and (EXTERNAL_ID like '%')
)
select metadatas1_.EXTERNAL_ID as col_0_0_
from m1 metadatas1_
inner join DG_PRODUCT dgproductf0_ on dgproductf0_.id=metadatas1_.product
left outer join CATALOG_ID catalogide2_ on metadatas1_.fk_catalogID=catalogide2_.ID
left outer join IMAGE_EXTENSION imageexten3_ on metadatas1_.id=imageexten3_.metadata
where
catalogide2_.profile='LEVEL0'
and dgproductf0_.archive_searchByAllUsers='t'
and (imageexten3_.begin_viewing_date_time between '2005-07-21 02:32:38.656' and '2008-07-17 16:09:20.128')
and imageexten3_.cloud_cover_percentage>='3.125'
limit 600

Forums PostgreSQL.fr

#1 27/06/2011 12:40:46

Index Gist non utlisé

#2 03/07/2011 23:54:58

Re : Index Gist non utlisé

Pied de page des forums